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

 

[JackDanIce]

Try:

Sub Find_Delete_Rows_v1()
    
    Dim x       As Long
    Dim str()   As Variant
    ReDim str(0 To 1)
    
    str(0) = "67016997 - 1"
    str(1) = "10000011823"

    Application.DisplayAlerts = False
    
    With ActiveSheet
    
        .DisplayPageBreaks = False
        
        If .AutoFilterMode Then .AutoFilterMode = False
        
        x = .Cells(.rows.Count, 1).End(xlUp).row
        
        With .Range("A1").Resize(x)
            .AutoFilter field:=1, Criteria1:=str, Operator:=xlFilterValues
            .Offset(1).Resize(x - 1).SpecialCells(xlCellTypeVisible).Delete
        End With
        
        .AutoFilterMode = False
        
    End With
    
    Erase str
        
    Application.DisplayAlerts = True
    
End Sub

The code applies a filter for the values you wish to delete, this is usually faster than looping through each row individually.

NB No need to .Select the ActiveSheet, since that sheet is already active

 

[pgc01]

Hi

... and you were missing the End If

If Not IsError(.Value) Then

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

[COLOR=#800000][B]End If[/B][/COLOR]

 

[Peter_SSs]

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

 

[ChristinaAC]

Thank you all for your help - seems to be working.

Do you know if there is a way to do a similar code but deleting any rows that do not start with "0" in column A?

Thank you very much

 

[hiker95]

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.

ChristinaAC,

If I understand you correctly, here is another macro solution for you to consider that does not do any looping thru column A.

Sample raw data:

Excel 2007
	A
1
2	AA 670164661 - 00001
3	BB 10000011823
4	AA670164661 - 00002
5	BB 10000011824
6
Sheet1

After the macro:

Excel 2007
	A
1
2	AA670164661 - 00002
3	BB 10000011824
4
5
6
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub ChristinaAC()
' hiker95, 10/13/2015, ME893919
Dim Addr As String
Addr = "A2:A" & Cells(Rows.Count, "A").End(xlUp).Row
Range(Addr) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""*670164661 - 00001*"",@)),""#N/A"",@)", "@", Addr))
Range(Addr) = Evaluate(Replace("IF(ISNUMBER(SEARCH(""*10000011823*"",@)),""#N/A"",@)", "@", Addr))
On Error GoTo MyExit
Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
MyExit:
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ChristinaAC macro.

 

[ChristinaAC]

Thank you very much, but I am concerned that the data download, that comes from a supplier offsite, may not always be the same. The data in column A, is all mobile numbers but for some reason it has these strange items (listed above) as well.

Is there a way to find and delete any rows that do not begin with 0?

Thank you so much and sorry to be a pain!

 

[hiker95]

Thank you very much

ChristinaAC,

You are very welcome. Glad I could help.

Is there a way to find and delete any rows that do not begin with 0?

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


3. So that we can get it right on the next try, can we see a screenshot of some of the mobile numbers?

Can you post a screenshot of the actual raw data worksheet?


To post a small screen shot (NOT a graphic, or, picture, or, PNG file, or, flat text) try the following:

Click on the below link to see How to display your sheet, and, how to install, download, and, use the MrExcel HTML Maker:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html

To test the above:
Test Here


The following is a free site:

You can upload your workbook to (the BLUE link-->) Box Net ,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

If the above site does not work for you, then try:
https://dropbox.com

 

[hiker95]

Is there a way to find and delete any rows that do not begin with 0?

ChristinaAC,

Here is a macro solution for you to consider based on your above quote.

Sample raw data:

Excel 2007
	A
1
2	001-555-1212
3	101-777-4444
4	516-507-1234
5	088-888-8888
6
Sheet1

After the macro:

Excel 2007
	A
1
2	001-555-1212
3	088-888-8888
4
5
6
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub KeepRowsStartingWith0()
' hiker95, 10/13/2015, ME893919
Dim Addr As String
Addr = "A2:A" & Cells(Rows.Count, "A").End(xlUp).Row
Range(Addr) = Evaluate("IF(ROW(),IF(LEFT(" & Addr & ")=""0""," & Addr & ",""""))")
Range(Addr).SpecialCells(xlBlanks).EntireRow.Delete
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the KeepRowsStartingWith0 macro.

 

[Peter_SSs]

... another macro solution for you to consider that does not do any looping thru column A.

Just a comment about looping, & this problem. I note that the OP states that there is "a large amount of data". I have no idea how big that means or what proportion of rows need to be deleted, which will affect the speed of some codes. Whilst my suggestion does contain a loop & contains more lines of code, my testing on about 10,000 rows of data produced these times to complete the task.

post #6 macro: 1.239 secs
post #4 macro: 0.109 secs (ie < 10%)