VBA code find multi text and delete entire rows

[ChristinaAC]

Hi there,

I have the below code of which I am trying to use on a large amount of data. The column that the search needs to look in is Column A, starting from cell A2. The text that it needs to find is "670164661 - 00001" and "10000011823", if it finds this content in any of the rows in column A, then it should delete the entire row.

Unfortunately there is an error with my code and but I'm not sure why, it keeps highlighting the second to last "Ends with", to say that its not needed.

Please let me know if you can help.

Thank you so much


Sub Findanddeleterows()

Dim FirstRow As Long
Dim LastRow As Long
Dim Looprow As Long
Dim CalcMode As Long
Dim ViewMode As Long

'creating the name for first and last row etc

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False

End With

With ActiveSheet
.Select
'selecting active sheet to use formula on

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
.DisplayPageBreaks = False
'if you are in page break/page layout view to normal view and turns off display page breaks

FirstRow = 2
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
'setting parameters for first row as cell 2 and for the formula to find the last row - this will enable the data level to continue to grow and for the formula to still work

For Looprow = LastRow To FirstRow Step -1
'Starting the find and delete on the last to first row less the heading
With Cells(Looprow, "A")
If Not IsError(.Value) Then

If .Value = "67016997 - 1" Then .EntireRow.Delete
If .Value = "10000011823" Then .EntireRow.Delete

End With
Next Looprow
End With

End Sub

 

[Aviles]

If you have a lot of data and the rows to delete are scattered through it, you may find this quicker.
Add your code about Application.Calculation if needed.

Sub RemoveThem()
  Dim LR As Long, LC As Long, i As Long, rws As Long
  Dim aCol, tmp
                              
  LR = Range("A" & Rows.Count).End(xlUp).Row
  LC = Cells(1, Columns.Count).End(xlToLeft).Column
  aCol = Range("A2:A" & LR).Value
  ReDim tmp(1 To LR - 1, 1 To 1)
  For i = 1 To LR - 1
      If aCol(i, 1) = "670164661 - 00001" Or aCol(i, 1) = "10000011823" Then
          rws = rws + 1
          tmp(i, 1) = 1
      End If
  Next i
  If rws > 0 Then
      Application.ScreenUpdating = False
      Cells(2, LC + 1).Resize(LR - 1).Value = tmp
      With Range("A2").Resize(LR - 1, LC + 1)
          .Sort Key1:=.Cells(1, LC + 1), Order1:=xlAscending, Header:=xlNo
          .Resize(rws).EntireRow.Delete
      End With
      Application.ScreenUpdating = True
  End If
End Sub

Hello @Peter_SSs or anyone else that could possibly help.
I didn't want to start a new thread as I thought the above code could help me with my problem:

I have a sheet with data in range A2:AG20000 (headings in row 1) and I would like to delete any rows that have the word "MARKET" in column L or that have a blank cell in Column L.
Using Peter's code, I changed the range to column L, instead of column A. Then I replaced the line of text that the OP wanted to find and replaced it with my two criteria.
The code I'm using is below which is not working for me. Can anyone please tell me what I'm doing wrong? Thanks in advance.

[COLOR=darkblue]Sub[/COLOR] RemoveThem()
  [COLOR=darkblue]Dim[/COLOR] LR [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], LC [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], rws [COLOR=darkblue]As[/COLOR] Long
  [COLOR=darkblue]Dim[/COLOR] aCol, tmp
                              
  LR = Range("L" & Rows.Count).End(xlUp).Row
  LC = Cells(1, Columns.Count).End(xlToLeft).Column
  aCol = Range("L2:L" & LR).Value
  [COLOR=darkblue]ReDim[/COLOR] tmp(1 [COLOR=darkblue]To[/COLOR] LR - 1, 1 [COLOR=darkblue]To[/COLOR] 1)
  [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] LR - 1
      [COLOR=darkblue]If[/COLOR] aCol(i, 1) = "MARKET" [COLOR=darkblue]Or[/COLOR] aCol(i, 1) = "" [COLOR=darkblue]Then[/COLOR]
          rws = rws + 1
          tmp(i, 1) = 1
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
  [COLOR=darkblue]Next[/COLOR] i
  [COLOR=darkblue]If[/COLOR] rws > 0 [COLOR=darkblue]Then[/COLOR]
      Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
      Cells(2, LC + 1).Resize(LR - 1).Value = tmp
      [COLOR=darkblue]With[/COLOR] Range("L2").Resize(LR - 1, LC + 1)
          .Sort Key1:=.Cells(1, LC + 1), Order1:=xlAscending, Header:=xlNo
          .Resize(rws).EntireRow.Delete
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
      Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
  [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

 

[Fluff]

The sort section should be

      With Range("[COLOR=#ff0000]A2[/COLOR]").Resize(LR - 1, LC + 1)
          .Sort Key1:=.Cells(1, LC + 1), Order1:=xlAscending, Header:=xlNo
          .Resize(rws).EntireRow.Delete
      End With

 

[Peter_SSs]

@Aviles
Apart from the point made by Fluff, to be sure your code does what is required, you will need to use a different method for determining LR.

With your current code, and Fluff's correction back to what my original code had for the sort, it would delete rows 3, 4 & 7 from the following sample data, but not rows 8 & 9 which also meet the deletion criteria.

Book1
	K	L
1	K	L
2	Data	Data
3	Data
4	Data
5	Data	Data
6	Data	Data
7	Data	MARKET
8	Data
9	Data
10
Del Rws

If you have particular column that can be relied upon to give the last row, say column B, then change the LR code to use that

LR = Range("B" & Rows.Count).End(xlUp).Row

Otherwise, if the extent of the data could depend on any column, then use this

LR = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Note also that your code is case-sensitive so would not delete rows with "market", Market", MarkeT" etc

 

[Aviles]

The sort section should be

      With Range("[COLOR=#ff0000]A2[/COLOR]").Resize(LR - 1, LC + 1)
          .Sort Key1:=.Cells(1, LC + 1), Order1:=xlAscending, Header:=xlNo
          .Resize(rws).EntireRow.Delete
      End With

@Aviles
Apart from the point made by Fluff, to be sure your code does what is required, you will need to use a different method for determining LR.

With your current code, and Fluff's correction back to what my original code had for the sort, it would delete rows 3, 4 & 7 from the following sample data, but not rows 8 & 9 which also meet the deletion criteria.

If you have particular column that can be relied upon to give the last row, say column B, then change the LR code to use that

LR = Range("B" & Rows.Count).End(xlUp).Row

Otherwise, if the extent of the data could depend on any column, then use this

LR = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Note also that your code is case-sensitive so would not delete rows with "market", Market", MarkeT" etc

Thank you both for your help!
Those amendments worked great and I'm learning along the way.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Peter_SSs]

Glad we could help & thanks for the feedback

Same from me.