VBA Code to delete rows based on cell data

[esmecat]

Hi,

I have some code which has worked for one workbook but is not working for another. It returns the data I want in Column G but also leaves a load of 0's which don't delete. Any help appreciated.



Sheets("Data Tab").Select

Dim Route As String

Application.ScreenUpdating = False

With Sheets("Data Tab")

Route = "G2:G" & Cells(Rows.Count, "G").End(xlUp).Row

Range(Route) = Evaluate(Replace("IF(@=""Anglia"",""#N/A"",@)", "@", Route))

Range(Route) = Evaluate(Replace("IF(@=""Central"",""#N/A"",@)", "@", Route))

Range(Route) = Evaluate(Replace("IF(@=""Kent"",""#N/A"",@)", "@", Route))

Range(Route) = Evaluate(Replace("IF(@=""London North Western (LNW)"",""#N/A"",@)", "@", Route))

Range(Route) = Evaluate(Replace("IF(@=""No Route Defined"",""#N/A"",@)", "@", Route))

Range(Route) = Evaluate(Replace("IF(@=""National/Project"",""#N/A"",@)", "@", Route))

Range(Route) = Evaluate(Replace("IF(@=""North West"",""#N/A"",@)", "@", Route))

Range(Route) = Evaluate(Replace("IF(@=""Scotland"",""#N/A"",@)", "@", Route))

Range(Route) = Evaluate(Replace("IF(@=""South East"",""#N/A"",@)", "@", Route))

Range(Route) = Evaluate(Replace("IF(@=""Sussex"",""#N/A"",@)", "@", Route))

Range(Route) = Evaluate(Replace("IF(@=""Wales"",""#N/A"",@)", "@", Route))

Range(Route) = Evaluate(Replace("IF(@=""Wessex"",""#N/A"",@)", "@", Route))

Range(Route) = Evaluate(Replace("IF(@=""West Coast Mainline South"",""#N/A"",@)", "@", Route))

Range(Route) = Evaluate(Replace("IF(@=""Western"",""#N/A"",@)", "@", Route))

Range(Route) = Evaluate(Replace("IF(@=""0"",""#N/A"",@)", "@", Route))

On Error GoTo NoDeletes

Columns("G").SpecialCells(xlConstants, xlErrors).EntireRow.Delete

End With

NoDeletes:

Application.ScreenUpdating = True

 

[Fluff]

Why not use replace instead

   With Sheets("Data Tab")
      With .Range("G2", .Range("G" & Rows.Count).End(xlUp))
         .Replace "Anglia", "#N/A", xlWhole, , False, , False, False
         .Replace "Central", "#N/A", xlWhole, , False, , False, False
      End With
   End With

You could also put all the words to be replaced in an array & then loop that, rather than writing a separate replace for each.