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

问题描述

好的，所以我要按条件过滤工作表(数据"):

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

然后，我想将过滤后的表格填充到列表框中 我的问题是，行的数量可以变化，因此我想我可以尝试通过执行此cells.find例程来列出已过滤表的结束"位置:

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

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

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?

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

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.

推荐答案

这是VBA代码，用于使用过滤后的行填充UserForm1.ListBox1.List. 感谢@FaneDuru改进了根据他的注释编辑的代码.

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.

在Userform1代码中

In Userform1 code

Private Sub UserForm_Initialize()
PopulateListBoxWithVisibleCells
End Sub

在模块中

Sub PopulateListBoxWithVisibleCells()

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

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

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

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