如何使用VBA将范围(多行和多列)的数据填充到列表框 [英] How to populate data from a range (multiple rows and columns) to listbox with VBA

问题描述

我在如何将具有多个列和行的范围内的数据放入列表框时遇到了麻烦.

I am having trouble with how to put the data from the range with multiple columns and rows to a listbox.

假设我有一个范围rng，其中有多个列和行 我试过了:

Assume I have a range rng which multiple columns and rows I tried:

如果我尝试了addItem rng(i，j)，那么所有内容都会在1列中.

If I tried addItem rng(i,j) then everything would be in 1 column.

我也尝试过.list，但是它也不起作用.

I also tried .list but it did not work either.

推荐答案

这是您要尝试的吗?

Option Explicit

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim rng As Range
    Dim i As Long, j As Long, rw As Long
    Dim Myarray() As String

    '~~> Change your sheetname here
    Set ws = Sheets("Sheet1")

    '~~> Set you relevant range here
    Set rng = ws.Range("A1:E5")

    With Me.ListBox1
        .Clear
        .ColumnHeads = False
        .ColumnCount = rng.Columns.Count

        ReDim Myarray(rng.Rows.Count, rng.Columns.Count)

        rw = 0

        For i = 1 To rng.Rows.Count
            For j = 0 To rng.Columns.Count
                Myarray(rw, j) = rng.Cells(i, j + 1)
            Next
            rw = rw + 1
        Next

        .List = Myarray

        '~~> Set the widths of the column here. Ex: For 5 Columns
        '~~> Change as Applicable        
        .ColumnWidths = "50;50;50;50;50"
        .TopIndex = 0
    End With
End Sub

快照

这篇关于如何使用VBA将范围(多行和多列)的数据填充到列表框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！