VBA userform list box to show visible result only from a filtered table

Hi,

I am trying to build a user form with a list box that shows a filtered result of a table. Currently, my code only shows the full table as obviously, the code ignores that my table is filtered and shows me the full table content. Could anyone please point me in the right direction of showing only the visible result in the grid? Thank you!

Please Login or Register  to view this content.

Try:

Please Login or Register  to view this content.

Instead of:

Please Login or Register  to view this content.

Hi CheeseSandwich, now the list box only shows the header row and nothing else......

What was visible in your filtered data at that point?

Are your headers in row 3? If so then Maybe:

Please Login or Register  to view this content.

Also as @Sintek stated, we could do with a file to work on - you have mentioned you are using a table, the way to define the range of a table is different from the way you work with ranges - we could have a look if we had a dummy file.

Hi ChesseSanwich and Sintek, I will try to make a dummy file now as the current one has 33 sheets and contains real customer data so I will make a fresh dummy file. And by the way. the below code

Please Login or Register  to view this content.

only shows the first line of the filtered table

Thanks sintek, thanks for the advice. The development did get bigger and it looks very messy now with 33 sheets 46 modules and 20 user forms as I didn't do much structure planning and put a lot of ad-hoc features on as I go along. I will try to tidy the project up. (Big task)

I was wondering, is there a way I can just copy the filtered table result to another sheet like a helper sheet then use the list box to show that as result?

Yes my bad on that one - forgot about the contiguous piece.

The attached is displaying only visible items from the table - you may be able to mimic this.

Thank you both for such wonderful solutions. I have used ChesseSandwich's code and this works perfect!!!!

Originally Posted by CheeseSandwich

Yes my bad on that one - forgot about the contiguous piece.

The attached is displaying only visible items from the table - you may be able to mimic this.

Hey CheeseSandwich, if after the filter is applied it has no result, it will give a 1004 error. I am trying to add error handling above your code to go to skip: but the error still pops up. Where should I add the error handling if no result after filtered is encountered? Thanks!

Hi I think I can just check to see if the table is not empty before deployment of your code by something like If TargetTable.DataBodyRange.VisibleRows.Count > 0

I have added some error handling around where it sets the table range 'filtRng', if the table is hidden entirely then this line will error. We can catch that error and return a message box then exit the sub.

Please Login or Register  to view this content.