esmecat,
Welcome to the MrExcel forum.
1. What version of Excel and Windows are you using?
2. Are you using a PC or a Mac?
Here is a Macro solution for you to consider, that does not do any looping thru the rows in column B of worksheet Sheet1.
You can change the worksheet name in the macro.
Please try the following macro on a copy of your workbook.
Code:
Sub esmecat()
'hiker95, 6/3/2019, ME1099777
Dim Addr As String
Application.ScreenUpdating = False
With Sheets("Sheet1")
Addr = "B2:B" & Cells(Rows.Count, "B").End(xlUp).Row
Range(Addr) = Evaluate(Replace("IF(@=""Carrot"",""#N/A"",@)", "@", Addr))
Range(Addr) = Evaluate(Replace("IF(@=""potato"",""#N/A"",@)", "@", Addr))
On Error GoTo NoDeletes
Columns("B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With
NoDeletes:
Application.ScreenUpdating = True
End Sub
So this has been working great on several macros over the last couple of weeks but I have just tried it again and have hit a snag.
Using the code below it is successfully deleting all but 'Anglia' which is being replaced with #N/A but the rows remain.
Dim Addr As String
Application.ScreenUpdating = False
With Sheets("Data Tab")
Addr = "A2:A" & Cells(Rows.Count, "A").End(xlUp).Row
Range(Addr) = Evaluate(Replace("IF(@=""Anglia"",""#N/A"",@)", "@", Addr))
Range(Addr) = Evaluate(Replace("IF(@=""Kent"",""#N/A"",@)", "@", Addr))
Range(Addr) = Evaluate(Replace("IF(@=""LNW North"",""#N/A"",@)", "@", Addr))
Range(Addr) = Evaluate(Replace("IF(@=""LNW South"",""#N/A"",@)", "@", Addr))
Range(Addr) = Evaluate(Replace("IF(@=""No Route Defined"",""#N/A"",@)", "@", Addr))
Range(Addr) = Evaluate(Replace("IF(@=""Scotland"",""#N/A"",@)", "@", Addr))
Range(Addr) = Evaluate(Replace("IF(@=""Sussex"",""#N/A"",@)", "@", Addr))
Range(Addr) = Evaluate(Replace("IF(@=""Wales"",""#N/A"",@)", "@", Addr))
Range(Addr) = Evaluate(Replace("IF(@=""Wessex"",""#N/A"",@)", "@", Addr))
Range(Addr) = Evaluate(Replace("IF(@=""Western Thames Valley"",""#N/A"",@)", "@", Addr))
Range(Addr) = Evaluate(Replace("IF(@=""Western West"",""#N/A"",@)", "@", Addr))
On Error GoTo NoDeletes
Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With
NoDeletes:
Application.ScreenUpdating = True