Excel VBA assigning values to an array

Question

I'm having issue assigning values to an array. Getting an out of range error when trying to assign

What I'm trying to do is loop through the rows in a table and assign certain values to load a listbox.

I've been using this:

Set rng = ws.Range("B1:C" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
With Me.lbWaste
    .Clear
    .ColumnHeads = False
    .ColumnCount = rng.Columns.Count
    '~~> create a one based 2-dim datafield array
     myArray = rng
    '~~> fill listbox with array values
    .List = myArray
    '~~> Set the widths of the column here. Ex: For 5 Columns
    '~~> Change as Applicable
    .ColumnWidths = "100;50;50"
    .TopIndex = 0
End With

but since it just copy and pastes the range values, there's hidden rows included in the data returned.

the columns I'm trying to copy to an array are b and c

how would I assign data to array using

for each rng in workbook1.sheets("Sheet1").listobjects("table1").databodyrange.rows
  if rng.entirerow.hidden = false then

?

Why not for each row in theListObject.ListRows?

Mathieu Guindon
– Mathieu Guindon

2023-01-16 13:54:53 +00:00
Commented Jan 16, 2023 at 13:54 — Mathieu Guindon
– Mathieu Guindon, Commented Jan 16, 2023 at 13:54

VBasic2008 · Accepted Answer · 2023-01-16 14:27:11Z

Populate a List Box With Values From a Discontinuous Range

... that has the same number of columns.

Sub PopulateWaste()
    
    ' Reference the visible range.
    
    Dim rg As Range
    Set rg = Sheet1.ListObjects("Table1").DataBodyRange.Columns("B:C")
        
    Dim vrg As Range
    On Error Resume Next
        Set vrg = rg.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    If vrg Is Nothing Then Exit Sub
    
    ' Write the values of each area of the range to an array in a collection.
    
    Dim coll As Collection: Set coll = New Collection
    
    Dim arg As Range, drCount As Long
    
    For Each arg In vrg.Areas
       drCount = drCount + arg.Rows.Count ' count the rows
       coll.Add arg.Value ' write
    Next arg
    
    ' Define the destination array.
    
    Dim cCount As Long: cCount = rg.Columns.Count
    Dim dData(): ReDim dData(1 To drCount, 1 To cCount)
    
    ' Loop through the collection and write the values from each array
    ' to the destination array.
    
    Dim sItem, sr As Long, c As Long, dr As Long
    
    For Each sItem In coll
        For sr = 1 To UBound(sItem, 1)
            dr = dr + 1
            For c = 1 To cCount
                dData(dr, c) = sItem(sr, c)
            Next c
        Next sr
    Next sItem
        
    Set coll = Nothing
    
    ' Write the values from the destination array to the list box.
    
    With Me.lbWaste
        .ColumnHeads = False
        .ColumnCount = cCount
        .List = dData
        .ColumnWidths = "100;50"
        .TopIndex = 0
    End With

End Sub

Collectives™ on Stack Overflow

Excel VBA assigning values to an array

1 Answer 1

Populate a List Box With Values From a Discontinuous Range

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Populate a List Box With Values From a Discontinuous Range

Comments

Your Answer

Sign up or log in

Post as a guest

Related