Listbox values from range question

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,311
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Some videos you may like

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
Joined
Jul 10, 2008
Messages
571
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,960
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top