# Listbox values from range question

#### sharky12345

##### Well-known Member
Can anyone help me change the range that my Listbox is populated from?

Code:
``````Dim j As Long
Dim x As Long
ReDim a(1 To 7, 2 To 30) As Variant
x = 1
For j = 2 To 30
If Not Range("A" & j).Value = vbNullString And Range("G" & j).Value = vbNullString Then
x = x + 1
a(1, x) = Range("A" & j).Value
a(2, x) = Range("B" & j).Value
a(3, x) = Range("C" & j).Value
a(4, x) = Range("D" & j).Value
a(5, x) = Range("E" & j).Value
a(6, x) = Range("F" & j).Value
'A(7, X) = Range("G" & j).Value
a(7, x) = j
End If
Next j
If x > 1 Then
ReDim Preserve a(1 To 7, 2 To x)
Me.ListBox1.List = Application.Transpose(a)
'ListBox1.ColumnWidths = ";;;;;;"
End If``````

Currently it starts at row 2, but I want it now to start at 33 and end at 62.

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Ragnar1211

##### Well-known Member
Perhaps:

Code:
``````Dim j As Long
Dim x As Long
ReDim a(1 To 7, 2 To 30) As Variant
x = 1
[COLOR=#ff0000]For j = 33 To 62[/COLOR]
If Not Range("A" & j).Value = vbNullString And Range("G" & j).Value = vbNullString Then
x = x + 1
a(1, x) = Range("A" & j).Value
a(2, x) = Range("B" & j).Value
a(3, x) = Range("C" & j).Value
a(4, x) = Range("D" & j).Value
a(5, x) = Range("E" & j).Value
a(6, x) = Range("F" & j).Value
'A(7, X) = Range("G" & j).Value
a(7, x) = j
End If
Next j
If x > 1 Then
ReDim Preserve a(1 To 7, 2 To x)
Me.ListBox1.List = Application.Transpose(a)
'ListBox1.ColumnWidths = ";;;;;;"
End If``````

#### sharky12345

##### Well-known Member
Perfect - thanks Gerry!

Replies
4
Views
48
Replies
4
Views
45
Replies
8
Views
44
Replies
0
Views
35
Replies
5
Views
131