Getting a range of data when rows are blank

Skippy

Board Regular
Joined
Mar 3, 2002
Messages
194
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?
Code:
varData = .Range(.Range("A2"), .Range("D65536").End(xlUp))
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Like this?

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

where column A contains the largest number of used rows.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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:

Code:
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]
 

Watch MrExcel Video

Forum statistics

Threads
1,118,071
Messages
5,570,023
Members
412,304
Latest member
citrus
Top