Macro to delete rows not containing specific text

[Alimou]

Hi,
I'm new to the forum...

Would anyone help me with a macro that:


1. Loops through all sheets in a workbook
2. Delete all rows in each sheet which don't contain 'Country', 'Spain' and "" (empty spaces) - The column to look for the values is column B in all sheets
3. Save the workbook as 'Spain.xls'

Many thanks

Ali

[ThisGuyJohn]

When you run this it will delete all the rows with blank spaces country and spain

Sub Filter()
    Dim b As Long
    With ActiveSheet
        For b = .Cells(Rows.Count, "b").End(xlUp).Row To 1 Step -1
            With .Rows(b)
                
                    If Not .Range("b1").Value = "Country" Then
                    If Not .Range("b1").Value = "Spain" Then
                    If Not .Range("b1").Value = vbNullString Then
                        .Delete
                    End If
                    End If
                    End If
                
            End With
        Next b
    End With
    
End Sub

[Alimou]

Thank you TGJ - I will give it a try.

What if I want this macro to loop through all existing worksheets except for the sheet named 'Frontpage'?

[ThisGuyJohn]

On the top of the code where it says With ActiveSheet

Sub Filter()
    Dim b As Long
    With ActiveSheet
        For b = .Cells(Rows.Count, "b").End(xlUp).Row To 1 Step -1
            With .Rows(b)

just add With "what ever the worksheet is called" - without the quotes
for example

With sheet2
With sheet3

then you need to add "end with" for each "with" you add I put in on the bottom

for Ex:

Sub Filter()
    Dim b As Long
    With ActiveSheet
With sheet2
With sheet3
With sheet4
With sheet5
        For b = .Cells(Rows.Count, "b").End(xlUp).Row To 1 Step -1
            With .Rows(b)
                
                    If Not .Range("b1").Value = "Country" Then
                    If Not .Range("b1").Value = "Spain" Then
                    If Not .Range("b1").Value = vbNullString Then
                        .Delete
                    End If
                    End If
                    End If
                
            End With
        Next b
    End With
End With
End With
End With
End With
    
End Sub

[Alimou]

Hi TJG,
I made the amendments and it worked. Thank you very much for your help.

A.