VBA - 填充列表框的过滤数据数组 [英] VBA - array of filtered data to populate listbox

问题描述

好的，我正在按标准过滤工作表(数据"):

Sub Filter_Offene()Sheets("Data").Range("A:R").AutoFilter Field:=18, Criteria1:="WAHR"结束子

然后，我想把过滤后的表填充一个列表框我的问题是，行数可能会有所不同，所以我想我可以尝试通过执行以下 cells.find 例程来列出过滤表结束"的位置:

Dim lRow As Long将 lCol 变暗lRow = ThisWorkbook.Sheets("Data").Cells.Find(What:="*", _之后:=范围(A1")，_看:=xlPart，_查找:=xl公式，_SearchOrder:=xlByRows, _搜索方向:=xlPrevious, _MatchCase:=False).RowlRow = lRow + 1

这不幸地也计算隐藏"行，所以在我的示例中它不计算 2 而是 7..我以前使用过 .Range.SpecialCells(xlCellTypeVisible)，但它似乎不适用于上面的 cells.find.有人对我如何计算可见(=过滤)表有想法，然后将其放入列表框中?

我像这样填充列表框(未过滤):

将lastrow变暗使用表格(数据")lastrow = .Cells(.Rows.Count, "R").End(xlUp).Row结束于使用 Offene_PZ_Form.Offene_PZ.ColumnCount = 18.ColumnWidths = "0;80;0;100;100;0;50;50;80;50;0;0;0;0;0;150;150;0".List = Sheets("Data").Range("A2:R" & lastrow).Value结束于

但这不适用于过滤后的数据.

解决方案

这是一个 VBA 代码，用于使用过滤的行填充 UserForm1.ListBox1.List.感谢@FaneDuru 根据他的评论对代码进行了改进.

在 Userform1 代码中

Private Sub UserForm_Initialize()PopulateListBoxWithVisibleCells结束子

在模块中

Sub PopulateListBoxWithVisibleCells()

将 wb 作为工作簿，ws 作为工作表Dim filtRng As Range, rw As RangeDim i As Long, j As Long, x As Long, y As Long, k As Long, filtRngArri = 0: j = 0: x = 0: y = 0设置 wb = ThisWorkbook:设置 ws = wb.Sheets("Sheet1")设置 filtRng = ws.UsedRange.Cells.SpecialCells(xlCellTypeVisible)对于 filtRng.Areas 中的每个区域x = x + Area.Rows.Count下一个y = filtRng.Columns.CountReDim filtRngArr(1 To x, 1 To y)对于 k = 1 到 filtRng.Areas.Count对于 filtRng.Areas(k).Rows 中的每个 rw我 = 我 + 1arr = rw.值对于 j = 1 到 yfiltRngArr(i, j) = Split(Join(Application.Index(arr, 1, 0), "|"), "|")(j - 1)下一个下一个下一个使用 UserForm1.ListBox1.ColumnCount = y.List = filtRngArr结束于结束子

我们还可以添加更多字段，例如行号，例如 Split(rw.Row & "|" & Join(Application.Index(arr, 1, 0), "|"),"|")(j - 1) 但是对于每个这样的预期列增量，我们需要增加 y 的值，例如 y = filtRng.Columns.Count + 1

为了找到 x(行数)，我们不需要第一个循环...简单地说，x = filtRng.Cells.Count/filtRng.Columns.Count 就足够了p>

Okay so I am filtering a sheet ("Data") by a criteria:

Sub Filter_Offene()
    Sheets("Data").Range("A:R").AutoFilter Field:=18, Criteria1:="WAHR"
End Sub

Then, I want to put the Filtered Table to populate a Listbox My problem here is, that the amount of rows can vary, so I thought i could try and list where the filtered table "ends" by doing this cells.find routine:

Dim lRow As Long
Dim lCol As Long

    lRow = ThisWorkbook.Sheets("Data").Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row

lRow = lRow + 1

This unfotunatly also counts "hidden" rows, so in my example it doesnt count 2 but 7.. I've used .Range.SpecialCells(xlCellTypeVisible)before, but It doesn't seem to function with the cells.find above. Does someone have an Idea on how I can count the visible (=filtered) Table, and then put it in a Listbox?

EDIT: I populate the listbox (unfiltered) like this:

Dim lastrow As Long
With Sheets("Data")
    lastrow = .Cells(.Rows.Count, "R").End(xlUp).Row
End With

With Offene_PZ_Form.Offene_PZ
.ColumnCount = 18
.ColumnWidths = "0;80;0;100;100;0;50;50;80;50;0;0;0;0;0;150;150;0"
.List = Sheets("Data").Range("A2:R" & lastrow).Value
End With

But this won't work with filtered Data.

解决方案

Here is a VBA code to populate UserForm1.ListBox1.List with filtered rows. Thanks to @FaneDuru for improvements in the code edited as per his comments.

In Userform1 code

Private Sub UserForm_Initialize()
PopulateListBoxWithVisibleCells
End Sub

In Module

Sub PopulateListBoxWithVisibleCells()

Dim wb As Workbook, ws As Worksheet
Dim filtRng As Range, rw As Range
Dim i As Long, j As Long, x As Long, y As Long, k As Long, filtRngArr
i = 0: j = 0: x = 0: y = 0

Set wb = ThisWorkbook: Set ws = wb.Sheets("Sheet1")

Set filtRng = ws.UsedRange.Cells.SpecialCells(xlCellTypeVisible)

For Each Area In filtRng.Areas
x = x + Area.Rows.Count
Next
y = filtRng.Columns.Count
ReDim filtRngArr(1 To x, 1 To y)

For k = 1 To filtRng.Areas.Count
For Each rw In filtRng.Areas(k).Rows
    i = i + 1
    arr = rw.Value
    For j = 1 To y
    filtRngArr(i, j) = Split(Join(Application.Index(arr, 1, 0), "|"), "|")(j - 1)
    
    Next
Next
Next

With UserForm1.ListBox1
.ColumnCount = y
.List = filtRngArr
End With

End Sub

We can also add more fields say row number like Split(rw.Row & "|" & Join(Application.Index(arr, 1, 0), "|"), "|")(j - 1) but for every such intended column increments, we need to increment value of y like y = filtRng.Columns.Count + 1

In order to find x (Number of rows) we don't need the first loop... Simply, x = filtRng.Cells.Count / filtRng.Columns.Count is enough

这篇关于VBA - 填充列表框的过滤数据数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！