[RESOLVED] How can I maipulate this basic code without going into a loop

[gphillips]

Sheets("Output1").Copy

Hi
I want to copy a worksheet called "output1" but only row 2 downwards to the "USED Range. Therefore skipping out row 1.

I then want to paste the contents in a template excel file called "Master" starting at cell A5.

I tried a loop copying eachcell in the used range in an array but its wayyyy to slow for something simple.

Please can somone advise the quickest method.

Cheers,

Boris.

[VBAhack]

One way that can be quicker is to assign the cells of interest directly to an array. You can then assign the array to the destination cells. See attached link for further explanation.

Dim iArray As Variant
    Dim nRows As Integer, nCols As Integer
    
    Application.ScreenUpdating = False
    With ActiveWorkbook
        'assign cells to an array
        iArray = .Sheets("Input").Range("A2:M15000")
        nRows = UBound(iArray, 1)
        nCols = UBound(iArray, 2)
        'assign array to cells
        .Sheets("Output").Range("A2:M15000") = iArray                   'method 1
        .Sheets("Output").Range("A5").Resize(nRows, nCols) = iArray     'method 2
    End With
    Application.ScreenUpdating = True

http://www.vbforums.com/showthread.php?t=391261

In the above snippet, you'll need to be careful with nRows & nCols. I can't remember off the top of my head whether the array is 0 or 1 based. Also, turning screen updating off can be a big time saver.

[Webtest]

This may help ... from the previous example ... assign only the used cells to your array:

    'Somewhere do this
    Dim aSht As Worksheet
    Set aSht = ActiveWorkbook.Sheets("Input")
    '     ...
    'assign cells to an array - from "A2" to Last Used Cell
    iArray = aSht.Range(aSht.Cells(2, "A"), aSht.Cells.SpecialCells(xlCellTypeLastCell))

Use "method 2" from the previous example. Thanks and credits to VBAhack.

[Static]

Sheets("Output1").Rows("2:" & ActiveSheet.UsedRange.Count).Select
Selection.Copy
Workbooks("Master").Sheets(1).Select
Range("A5").Select
ActiveSheet.Paste

[Webtest]

Static:

You probably mean:

Sheets("Output1").Rows("2:" & ActiveSheet.UsedRange.Rows.Count).Select

Nice approach ... the "UsedRange" limits the selection to the proper number of columns.

[gphillips]

Hi Guys,

What a fine bit of coding. This is trully excellent.

I must admit the LBOUND is over my head in term of technicality. Don't reallly understand this. I might stick with the used range.

Thanks for the advice, this is great post in term of usefulness.

Borris.

[Webtest]

Just type "lbound" into an otherwise empty Macro, put the cursor somewhere in the word, and hit the <F1> key. The HelpHeap will pop up an explanation of the Function. LBound is just the lowest index number for an array ... generally zero, sometimes 1.

[Static]

and to add to that.. UBOUND() is the upper index of an array

Example:

Dim sArray(1 to 10) as String
Msgbox LBound(sArray)
Msgbox UBound(sArray)

the first msgbox will give you "1"
the second will give you "10"

[gphillips]

nRows = UBound(iArray, 1)
        nCols = UBound(iArray, 2)
        'assign array to cells
        .Sheets("Output").Range("A2:M15000") = iArray                   'method 1
        .Sheets("Output").Range("A5").Resize(nRows, nCols) = iArray

Hey guys,

Does the 1 and 2 of UBOUND's formulas refer to the inteligence if telling the computer set the highest index of 1 = rows, 2 = columns.


Is this siimilar principle to:-

Dim A(1 To 100, 0 To 3)

1to 100 = rows, 0 to 3 = columns.


What about resize?

Thanks,
Borris



Dim

[VBAhack]

Your question should probably should be a separate thread, but:

Dim intArray() As Integer
    
    ReDim intArray(0 To 5) As Integer                       'values initialized to 0
    intArray(1) = 23
    Debug.Print UBound(intArray), intArray(1)               '==> 5  23
    
    ReDim intArray(1 To 10) As Integer                      'values initialized to 0
    Debug.Print UBound(intArray), intArray(1)               '==> 10 0
    
    ReDim intArray(0 To 5, 0 To 10) As Integer              'values initialized to 0
    Debug.Print UBound(intArray, 1), UBound(intArray, 2)    '==> 5 10
    
    ReDim intArray(1 To 10, 5 To 20) As Integer             'values initialized to zero
    Debug.Print UBound(intArray, 1), UBound(intArray, 2)    '==> 10 20
    Debug.Print intArray(1, 5)                              '==> 0
    
    intArray(1, 5) = 16
    ReDim Preserve intArray(1 To 10, 5 To 50) As Integer    'values NOT initialized
    Debug.Print UBound(intArray, 1), UBound(intArray, 2)    '==> 10 50
    Debug.Print intArray(1, 5)                              '==> 16
    
'    ReDim Preserve intArray(1 To 30, 5 To 50) As Integer   'error - can't change 1st dimension