Getting a range of data when rows are blank

[Skippy]

Can't seem to figure the syntax for this. I want to fill a listbox with all the data from Columns A to D in a worksheet. The following works fine if Columns A and D have the same number of filled rows. But I have, say 200 rows in Column A and only 30 in Column D. How can I get 200 rows of data for Columns A to D (including blanks) when the columsn are of different lenght?

varData = .Range(.Range("A2"), .Range("D65536").End(xlUp))

 

[Andrew Poulsom]

Like this?

varData = .Range("A2:D" & .Range("A65536").End(xlUp).Row)

where column A contains the largest number of used rows.

 

[Richard Schollar]

If the exact column that will contain the largest number of rows isn't known in advance then you could use something aqlong the lines of:

Sub test()

Dim myarray(1 To 4) As Long, i As Integer, vardata As Variant

For i = 1 To UBound(myarray)
    myarray(i) = Cells(65536, i).End(xlUp).Row
Next i
With Sheets("sheet1")
vardata = .Range(.Range("A2"), .Range("D" & Application.WorksheetFunction.Max(myarray)))
End With


End Sub

Regards

Richard[/i]

 

[Skippy]

Thanks for the replies... works great!