VBA -- Filtered Cells Value to a Listbox

[Koth851]

Good Afternoon,
I try to optimize my master excel file by adding some macros. I looked over different forums, but I still don't understand why my code doesn't work.

I click on the commandbutton: CommandButton_Stage1.

First, it has to remove every filter.
Then, I want to filter the data at the cell "A4". (sheet Projects_List)

After, I want to use that filtered information (under the cell "C4") into a Listbox.
I want to expand the cell selection until it reaches a blank cell.

Here's my code:

Private Sub CommandButton_Stage1_Click()


Sheets("Projects_List").Select
ActiveSheet.AutoFilterMode = False
ActiveSheet.Range("$A$4:$J$20").AutoFilter Field:=1, Criteria1:="1"




Set rRange = Range("C4", Range("C4").End(xlDown)).Cells.SpecialCells(xlCellTypeVisible)


ListBox_Projects.RowSource = rRange.Address

End Sub

Thanks for your help,
Its appreciated.

 

[Akuini]

You can't fill the 'Rowsource' property with a filtered range.
There's no straightforward way to populate a listbox with a filtered range. If you're filtered range is small, say about 1000 row, then
using 'ListBox_Projects.Additem' method will be fast enough.
But if your data is big then we need another approach.

Private Sub CommandButton_Stage1_Click()

Sheets("Projects_List").Select
ActiveSheet.AutoFilterMode = False
ActiveSheet.Range("$A$4:$J$20").AutoFilter Field:=1, Criteria1:="1"

Set rRange = Range("C4", Range("C4").End(xlDown)).Cells.SpecialCells(xlCellTypeVisible)
ListBox_Projects.Clear
For Each c In rRange
ListBox_Projects.AddItem c
Next

End Sub

Another note:
you may want to change this part:

Set rRange = Range("C4", Range("C4").End(xlDown)).Cells.SpecialCells(xlCellTypeVisible)

to this:

Set rRange = Range("C4", Cells(Rows.count, "C").End(xlUp)).SpecialCells(xlCellTypeVisible)

Using 'End(xlDown)' is not a reliable way to find the last row with data.

 

[Koth851]

Thanks Gents!
It works perfectly!
That's Great

 

[Akuini]

Thanks Gents!
It works perfectly!
That's Great

You're welcome & thanks for replying