Excel VBA ListBox in User Form Populate data from Sheet Range, add row by row after evaluating for a condition

Question

I am trying to write a VBA code where I want to populate DATA from a worksheet Range A to AQ spanning over multiple Rows. AQ contains Value "Open" or "Closed". I want to get the rows where AQ value is closed. I tried using the AutoFilter. This is working fine to an extent. But I have to use 2 For loops. One for Each Row and another for Each Column to populate Row wise, column by column into the list box

My Code as follows: Note : Actual contents start from 6th Row where 6 contains the headers and data starts from 7th Row

Dim i As Long
Dim rowRange As Range

Dim AllData(1 To 1000, 1 To 43) As String
lstRecords.ColumnCount = 43

Set shDSR = mydata1.Sheets("DSR")
last_Row = shDSR.Cells(Rows.Count, 1).End(xlUp).Row 
 
    shDSR.AutoFilterMode = False
    shDSR.Range("A6:AQ" & last_Row).AutoFilter Field:=43, Criteria1:="CLOSED"

    Set rng = shDSR.Range("A6:AQ" & last_Row).SpecialCells(xlCellTypeVisible)
    
    Dim filtrRow() As String
    Dim rowCnt As Long
    'Me.lstRecords.Clear
    rowCnt = 0
     If rng.Count > 0 Then
        Me.lstRecords.Clear
        
        Me.lstRecords.ColumnCount = rng.Columns.Count   
        For Each Row In rng.Rows
            Me.lstRecords.AddItem
            rowCnt = rowCnt +1
            filterRow = Range(Row.Address)
           'Me.lstRecords.List() = filterRow  ''This throws error Type Mismatch so not using
            For i = 1 To Row.Columns.Count
              AllData(rowCnt, i) = Row.Cells(1, i).Value ''Move to Array
              Me.lstRecords.List(rowCnt - 1, i - 1) = filterRow(1, i)'Buggy error when i = 11
            Next
        Next
      '' Following segment works. Add data to Array and then populate ListBox from Array        
     Me.lstRecords.List() = AllData 
     Else
        MsgBox "No data matches the filter criteria."
    End If

Above Code has both approaches a) Trying to load directly from excel Range (actually using filterRow, but can also directly use range with same issue). But, this approach stops always when i=11 with Invalid property error. I tried changing the data contents etc still same issue Another Issue when Not taking the array based approach, only one line is added, so in affect only last line is available in the list box b) Using the AllData array. I load all the row data (matching criteria) into the array and finally populate the listbox from array. THIS WORKS. But I do not like this approach Can some one please point out where it is going wrong. Thanks in advance

CDP1802 · Accepted Answer · 2023-01-15 15:57:38Z

0

Problem is that filters create a non contiguous range consisting of areas which you have to iterate separately.

Option Explicit
Sub demo()

    Dim mydata1 As Workbook, shDSR As Worksheet
    Dim rng As Range, a As Range, r As Range
    Dim last_row As Long, n As Long
    Dim i As Long, rowCnt As Long
    Dim ListData() As String
    
    ' change this
    Set mydata1 = ThisWorkbook
    
    Set shDSR = mydata1.Sheets("DSR")
    With shDSR
          .AutoFilterMode = False
         last_row = .Cells(.Rows.Count, "AQ").End(xlUp).Row
         .Range("A6:AQ" & last_row).AutoFilter Field:=43, Criteria1:="CLOSED"
         Set rng = .Range("A6:AQ" & last_row).SpecialCells(xlCellTypeVisible)
         .AutoFilterMode = False
    End With
    
    ' clear listbox
    With Me.lstRecords
        .Clear
        .ColumnCount = rng.Columns.Count
    End With
    
    'iterate areas and rows to count visible rows
    For Each a In rng.Areas
        n = n + a.Rows.Count
    Next
    
    rowCnt = 0
    If n > 1 Then
        ' size array
        ReDim ListData(1 To n, 1 To rng.Columns.Count)
    
        ' fill array
        For Each a In rng.Areas
            For Each r In a.Rows
                rowCnt = rowCnt + 1
                For i = 1 To UBound(ListData, 2)
                    ListData(rowCnt, i) = r.Cells(1, i).Value ''Move to Array
                Next
            Next
         Next
         ' populate ListBox from Array
         Me.lstRecords.List() = ListData
     Else
         MsgBox "No data matches the filter criteria."
    End If

End Sub

answered Jan 15, 2023 at 15:57

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

suresh Over a year ago

Hi , Thanks for your inputs. I wanted to know if instead of using the array approach, whether I can directly get data from the Sheet. Also thanks for correctly the code

CDP1802 Over a year ago

@suresh You can only fill 10 columns of the listbox directly which is why you get an error with i = 11.

Collectives™ on Stack Overflow

Excel VBA ListBox in User Form Populate data from Sheet Range, add row by row after evaluating for a condition

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related