Using VBA to add a filter, filter by certain criteria, then delete filtered rows

**webcam723** · 07-10-2023, 10:02 AM

Hello! I would really appreciate the help with a VBA problem here.

I have attached the working file

There are 2 sheets in the workbook: the data source and the "End Goal" sheet as a reference.

Using a VBA code, I wish to apply a filter to the data source sheet, filter Column B to only show 'Singapore', and filter Column C to only show 'Folk', 'Indie', 'Rock'.
Then delete all the filtered out rows.

I expect to have more than 1 sheet of data, how can we expand the above to apply to every sheet in the workbook (preferably starting from the second sheet).

Test.xlsm

**vinitdhuri** · 07-10-2023, 10:25 AM

I Suggest you go for Power Query option for this requirement instead of VBA. These are inbuilt function with higher reliability and flexibility.

M Code (added by Moderator):

Please Login or Register  to view this content.

**Marc L** · 07-10-2023, 11:07 AM

( removed )

**Marc L** · 07-10-2023, 11:36 AM

A ThisWorkbook module Excel basics VBA demonstration for starters :

PHP Code:


Sub Demo1() 
    With Sheets(1).[AF2] 
        .Formula = "=(B2<>""Singapore"")+((C2<>""Folk"")*(C2<>""Indie"")*(C2<>""Rock""))>0" 
    For S& = 2 To Sheets.Count 
    With Sheets(S).[A1].CurrentRegion 
        .AdvancedFilter 1, Sheets(1).[AF1:AF2] 
         If Application.Subtotal(103, .Columns(1)) > 1 Then .Offset(1).Rows.Delete xlShiftUp 
        .Parent.ShowAllData 
    End With 
    Next 
        .Clear 
    End With 
End Sub

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

**webcam723** · 07-10-2023, 11:43 AM

If I have more than 1 data source sheet to comb through and still want to use the same filters for every sheet, how can loop through all the sheets with the same code?

**Marc L** · 07-10-2023, 11:45 AM

Just see post #4 at least !

**webcam723** · 07-10-2023, 09:24 PM

Hi, it doesn't work.

S& is not defined. And .Formula line has error too.

**jindon** · 07-10-2023, 11:27 PM

try

Please Login or Register  to view this content.

**Marc L** · 07-11-2023, 05:08 AM

Originally Posted by webcam723

Hi, it doesn't work.

As it well works on my side …

Using VBA to add a filter, filter by certain criteria, then delete filtered rows

LinkBack

Thread Tools

Rate This Thread

Display

Using VBA to add a filter, filter by certain criteria, then delete filtered rows

Re: Using VBA to add a filter, filter by certain criteria, then delete filtered rows

Re: Using VBA to add a filter, filter by certain criteria, then delete filtered rows

Hello, try this !

Re: Using VBA to add a filter, filter by certain criteria, then delete filtered rows

Re: Using VBA to add a filter, filter by certain criteria, then delete filtered rows

Re: Using VBA to add a filter, filter by certain criteria, then delete filtered rows

Re: Using VBA to add a filter, filter by certain criteria, then delete filtered rows

Re: Using VBA to add a filter, filter by certain criteria, then delete filtered rows

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Filter Column > Select All Filtered Rows exc. Header > Delete Rows

Filter table by multiple criteria and delete all irrelevant rows

Select and Delete rows without using filter using multiple criteria

Filter common criteria from 2 columns & filter large of filtered values from 3rd column

Delete Rows hidden by either Auto Filter and Advanced Filter

delete rows based on a filter criteria - VBA

Auto Filter Delete Rows by Criteria Doesn't Work Range To Complicated

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions