VBA Language for multiple AutoFilter ranges within a single sheet

First off, I'm a beginner when it comes to VBA and even macros. I use excel for daily task but I consider myself experienced among rookies.

The issue I'm having here is I need AutoFilter to apply to my sheet (and then some other sheets), but only within specific ranges, as I have multiple (5+) tables per sheet.

I tried doing this with the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet1").AutoFilter.ApplyFilter
End Sub

Basic, I know. I got it off google. I dont know VBA or any other programming language for that matter (I'm a consultant, you can imagine the things we use excel for are not cutting edge). But it works - the filters in the tables only sort the data (largest to smallest), they dont actually filter it.

The problem with this code is that every time I try to change any data outside of the tables with filters, even an empty cell at column xmd, I get "runtime error 91" - the led me to believe that the code is working on the whole spreadsheet rather than just the tables with filters.

So what I'm thinking I need here is for the code to work only of the selected ranges of my sheet, for example the first table's range is C9:F15 (Row 9 is for the Headers), the second table is C25:C31 (row 25 being headers again).

Could anyone please help me here? I realize that its a big ask, but it would be much appreciated. Let me know if you need any other details here.

Thank you,
Rodrigo

Best way to learn VBA is to:
Record macros >> see and modify the code.

If you donno how to record macro in Excel, Search the video in youTube.

Maybe you need to check out the AutoFilterMode Flag before action, for example:

Please Login or Register  to view this content.

The AutoFilter object is used in two contexts. In the context of a worksheet and in the context of a Table. We use the first way when we are NOT using a Table (ListObject). In that case, there can be only one data range for Autofilter. The second way, of course, applies to Tables.
The event code shown refers to Autofilter in the context of a sheet, and since you have Tables, you get an error.
If there was only one Table in the sheet, you could check if the cell being changed belongs to that Table like this:

Please Login or Register  to view this content.

Since you have several Tables, you first need to check which Table the cell being changed belongs to. And only then perform the action on the specified Table.

Try using the following code

Please Login or Register  to view this content.

If you change a cell outside of any of the tables, the table in the For Each...Next loop will not be found and the LO variable will contain Nothing. So the condition

Please Login or Register  to view this content.

will not be executed.

You can use the presented code construction when you have many tables in the sheet, but also for a single table.

Artik

Hi Artik,

It looks like this did the trick. You are awesome man. I wish all the beers in your future are of the perfect temperature!

Thank you,
Rodrigo