5

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.

Improve this question
6
  • Already answered here .. Row count on the Filtered data .. Just make sure you have filtered data in the column referred in the function. If you data starts in Column B, refer column B Commented May 30, 2020 at 8:32
  • 1
    The SpecialCells(xlCellTypeVisible) range of a filtered range is a discontinue one in most of cases. So, in order to obtain what you need, it is also necessary to state how would you like to load it. All the filtered range, or only the values in one column? Do you want linking it to a range, or loading the items separately? Then, you must limit the area range to the last cel, not to all columns... Commented May 30, 2020 at 8:34
  • @FaneDuru I would like to show the filtered rows into my userform-listbox. But my brain doesn't seem to get there; I can put in the non filtered data, and i am doing it like you can see in my Edit in my Question Commented May 30, 2020 at 8:46
  • 1
    @NareshBhople Thanks. i had a look at it, but I don't know if i need that. I just need a simple solution on populating the listbox with the data thats visible on the worksheet (so after filtering) Commented May 30, 2020 at 8:50
  • Your code works in continuous ranges, which creates a continuous 2D array which can be split by rows and column when you load it in the List of list box. In such a case (of filtered, discontinuous range) an iteration between the filter range areas and build an array for the specific visible cells row/slice would be necessary, I think. Or load each list box column one by one when the iteration reach a row of the filtered range... Commented May 30, 2020 at 8:59

3 Answers 3

Reset to default
3

Here is a fun little fact, Excel creates an hidden named range once you start filtering data. If you have continuous data (headers/rows) this would return your range without looking for it. Though since it seem to resemble UsedRange it may still be better to search your last used column and row and create your own Range variable to filter. For this exercise I'll leave it be. Furthermore, as indicated in the comments above, one can loop over Areas of visible cells. I'd recommend a check beforehand just to be safe that there is filtered data other than headers.

Sub Test()

Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Data")
Dim Area as Range

ws.Cells(1, 1).AutoFilter 18, "WAHR"    
With ws.Range("_FilterDatabase")
    If .SpecialCells(12).Count > .Columns.Count Then
        For Each Area In .Offset(1).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(12).Areas
            Debug.Print Area.Address 'Do something
        Next
    End If
End With

End Sub

The above works if no headers are missing obviously.

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

6 Comments

It errors for Variable Area: Variable is not defined?
Ah, you got Option Explicit on the top. Very good. I'm lazy, but yeah you should have a range variable for Area then =). Adjusted.
Holy heck. just pasted it in, checked the debug and DAMN IT WORKS! Thanks alot. I swear I was so pent up because of it... okay. so now I have all the rows!
uh another thing.. i never worked with arrays (not 2D ones).. And i think the best way is to create an array with the ranges' values and then paste them into the listbox? or can you add an entry to the listbox directly with eacht for loop=
@LeonS, I would say you could Dim arr() as Variant and after the Columns.Count check you ReDim arr(0 To .SpecialCells(12).Rows.Count, 1 To .Columns.Count). Then you just keep a counter and fill the array when looping these Areas. Then when that's done you throw the array in the ListBox in a single go.
|
2

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

enter image description here

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

Improve this answer

11 Comments

Information in comments can't be considered an answer. So additional answers mentioning this are welcome nonetheless. +
This code will work only in 'special' circumstances. I mean, only if each area will have a single row which, in case of filtering, happens very seldom. So, x cannot be the areas Count, except in the case mentioned above. Then, the code includes the columns headers, too, which is not a normal practice when loading a combo...
@FaneDuru . I tried adding a loop to count number of rows first from the areas. That gave me x to redim the filtRngArr array. Then in the original loop I added one more loop for areas.count. It helped. Thanks for the inputs.
Nice idea (at least, good looking...) to use an array slice, but, please modify it using i instead of 1. Otherwise, it will repeat the first row of the area in case of more rows... Anyhow, I will vote it up (especially, for this approach). It takes similar time like iteration (a little more, in fact), but it looks nice... :)
You are right, sorry... I looked at your code having the reflex of mine, when iterate between array rows. I tested your idea on my code and there it needs this adaptation. I did not look carefully to your code.
|
1

Try, please the next code, if you want to use a continuous (built) array. It is possible to build it from the discontinuous range address, too:

    Sub Filter_Offene()
      Dim sh As Worksheet, lastRow As Long, rngFilt As Range, arrFin As Variant

      Set sh = Sheets("Data")
      lastRow = sh.Range("R" & Rows.count).End(xlUp).Row
        rngFilt.AutoFilter field:=18, Criteria1:="WAHR"

        Set rngFilt = rngFilt.Offset(1).SpecialCells(xlCellTypeVisible)

        arrFin = ContinuousArray(rngFilt, sh, "R:R")

        With ComboBox1
            .list = arrFin
            .ListIndex = 0
        End With
    End Sub

    Private Function ContinuousArray(rngFilt As Range, sh As Worksheet, colLet As String) As Variant
        Dim arrFilt As Variant, El As Variant, arFin As Variant
        Dim rowsNo As Long, k As Long, i As Long, j As Long, arrInt As Variant

        arrFilt = Split(rngFilt.address, ",")' Obtain an array of areas addresses
        'real number of rows of the visible cells range:
        For Each El In arrFilt
             rowsNo = rowsNo + Range(El).Rows.count
        Next
        'redim the final array at the number of rows
        ReDim arFin(1 To rowsNo, 1 To rngFilt.Columns.count)

        rowsNo = 1
        For Each El In arrFilt            'Iterate between the areas addresses
            rowsNo = Range(El).Rows.count 'number of rows of the area
            arrInt = ActiveSheet.Range(El).value' put the area range in an array
            For i = 1 To UBound(arrInt, 1) 'fill the final array
                k = k + 1
                For j = 1 To rngFilt.Columns.count
                     arFin(k, j) = arrInt(i, j)
                Next j
            Next i
        Next
    ContinuousArray = arFin
End Function
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.