Delete Row based on cell value

[esmecat]

Hi

I have read several threads on this but just can't seem to get this to work for my criteria.

I have a range of data that varies week to week by number of rows.

I would like to delete all rows that contain a certain word e.g. "Carrot" in column B of the data

I would then like to also delete all rows that contain the word "potato", also in column B of the data

Any help would be appreciated.

 

[vcoolio]

Hello Esmecat,

See if the following code works for you:-

Sub Test()

Application.ScreenUpdating = False

With Sheet1.[A1].CurrentRegion  '----> Change sheet code to suit.
        .AutoFilter 2, "Carrot", xlOr, "Potato"
        .Offset(1).EntireRow.Delete
        .AutoFilter
End With

Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[esmecat]

Hi

I have currently done it like this but cant get it to accept the second word.
Dim iLastRow As Long
Dim i As Long
iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "B").Value = "Carrot" Then
Rows(i).Delete
End If
Next i

End Sub

 

[vcoolio]

Hello Esmecat,

Using the code that you have, try it amended as follows:-

Sub Test2()

Dim i As Long

iLastRow = Cells(Rows.Count, "B").End(xlUp).Row

For i = iLastRow To 1 Step -1
      If Cells(i, "B").Value = "Carrot" [COLOR=#ff0000]Or Cells(i, "B").Value = "Potato"[/COLOR] Then
      Rows(i).Delete
      End If
Next i

End Sub

The bit added in red font should sort that out for you.

Try the code in post #2 as using autofilter instead of a For/Next loop is a more efficient, faster method especially if you have a large data set.

I hope that this helps.

Cheerio,
vcoolio.

 

[esmecat]

Thank you both.

I couldn't get the auto filter to work at all so I must be doing something wrong there!

 

[hiker95]

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.

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

 

[vcoolio]

Hello Esmecat,

The code in post #2 may not be working for you as it assumes that you have headings in Row1 with data starting in Row2 or the sheet reference(Sheet1) is incorrect. This could be causing the code to error. Please advise how your data is set out and if the sheet reference is correct.

Does your code, as amended in post #4 , work?

Cheerio,
vcoolio.

 

[esmecat]

Thanks for the welcome and the code.
It works perfectly thank you.

 

[hiker95]

Thanks for the welcome and the code.
It works perfectly thank you.

esmecat,

You are very welcome.

Glad I could help.

And, come back to MrExcel anytime.

 

[esmecat]

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.

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