Delete Row based on cell value

esmecat

New Member
Joined
May 5, 2010
Messages
18
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

Well-known Member
Joined
Jun 29, 2014
Messages
856
Hello Esmecat,

See if the following code works for you:-


Code:
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

New Member
Joined
May 5, 2010
Messages
18
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

Well-known Member
Joined
Jun 29, 2014
Messages
856
Hello Esmecat,

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


Code:
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

New Member
Joined
May 5, 2010
Messages
18
Thank you both.

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

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,648
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
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
856
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

New Member
Joined
May 5, 2010
Messages
18
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
 

Forum statistics

Threads
1,085,648
Messages
5,384,914
Members
401,926
Latest member
healthguy

Some videos you may like

This Week's Hot Topics

Top