#1
|
|||
|
|||
Macro to delete rows not containing specific text
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 |
#2
|
|||
|
|||
When you run this it will delete all the rows with blank spaces country and spain
Code:
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 |
#3
|
|||
|
|||
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'? |
#4
|
|||
|
|||
On the top of the code where it says With ActiveSheet
Code:
Sub Filter() Dim b As Long With ActiveSheet For b = .Cells(Rows.Count, "b").End(xlUp).Row To 1 Step -1 With .Rows(b) 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: Code:
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 |
#5
|
|||
|
|||
Hi TJG,
I made the amendments and it worked. Thank you very much for your help. A. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Macro to delete text in specific styles | ljd108 | Word VBA | 14 | 01-22-2019 01:22 PM |
Macro to delete text from cells with specific format | Soenke | Word VBA | 4 | 09-01-2016 08:55 AM |
Macro to delete all empty rows from all tables | braddgood | Word VBA | 15 | 10-02-2015 01:54 PM |
Macro to conditionally delete rows | Steve_D | Excel | 2 | 08-24-2012 09:37 PM |
Macro to delete rows with all empty cells | ubns | Excel Programming | 2 | 08-14-2012 02:01 AM |