如何将Excel指定列的最后5行数据填充到VBA UserForm List控件？

Here's a reliable VBA implementation to populate your ListBox with data from the last 5 rows of specified columns (A, B, D, G):

Step-by-Step Code Implementation #

Add this code to your UserForm's Initialize event (adjust sheet name and ListBox control name as needed):

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim startRow As Long
    Dim rowIndex As Long
    Dim targetCols As Variant
    Dim colIndex As Integer
    
    ' Reference your target worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace with your sheet name
    
    ' Define the columns you want to fetch (A=1, B=2, D=4, G=7)
    targetCols = Array(1, 2, 4, 7)
    
    ' Configure ListBox to match the number of selected columns
    Me.ListBox1.ColumnCount = UBound(targetCols) + 1
    
    ' Find the last used row in the worksheet (using column A as anchor)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Calculate starting row to get last 5 rows (handle cases with <5 rows)
    startRow = IIf(lastRow < 5, 1, lastRow - 4)
    
    ' Clear existing ListBox items before populating
    Me.ListBox1.Clear
    
    ' Loop through each row from startRow to lastRow
    For rowIndex = startRow To lastRow
        ' Add empty row to ListBox
        Me.ListBox1.AddItem
        
        ' Populate each column in the ListBox row with target worksheet values
        For colIndex = LBound(targetCols) To UBound(targetCols)
            Me.ListBox1.List(Me.ListBox1.ListCount - 1, colIndex) = ws.Cells(rowIndex, targetCols(colIndex)).Value
        Next colIndex
    Next rowIndex
End Sub

Key Details to Note #

• Column Number Mapping: Excel columns are 1-indexed, so we map A→1, B→2, D→4, G→7 in the targetCols array. Adjust this array if you need different columns.
• Last Row Detection: The code uses column A to find the last row. If your data has empty cells in column A, replace "A" with a column that always contains data for accurate last row detection.
• Small Dataset Handling: The IIf statement ensures we start at row 1 if there are fewer than 5 rows in the sheet, avoiding errors.
• ListBox Configuration: Setting ColumnCount matches the number of selected columns, so data displays correctly in separate columns.

Common Fixes for Failed Loop Attempts #

If your previous loop didn't work, check for these issues:

• Forgetting to set ColumnCount on the ListBox (leads to all data being dumped into a single column)
• Using column letters instead of numbers in the loop (Excel VBA requires column indices for ListBox.List)
• Not handling cases where there are fewer than 5 rows (causes negative start row values)

内容的提问来源于stack exchange，提问作者Filips Zoltners