How to Delete Rows of Text After Finding Specific Words in Excel and continue deleting rows until it finds other specific words

Question

I'm relatively new to the whole Visual Basic scene but do have basic knowledge in programming and I understand how to read code and follow the logic patterns.

What I am trying to accomplish in this macro is the following:

Find the row in the spreadsheet that says the word "Suppressed" then delete that row and the following rows until it finds the row which says "Other Response Categories" in it, and stop there without deleting that from the row.

Find the row in the spreadsheet that says the words "Requires Challenge Response" and delete that row along with all rows underneath it until it finds a row which has a line of text in it named "Tracking Links Clicked" and stop there without deleting that from the row.

Find the row in the spreadsheet that says the words "Link Name (HTML)" and delete that row along with all rows underneath it.

I have used the "Record Macro" function to get a general idea on how to remove lines of text from excel but only by using ranged areas which are selected then deleted; not searching for key phrases.

I'm in the works on researching a lot of VB stuff in order to actually write what I want to accomplish.

Edit2: So I modified and simplified down the VB code that you provided to at least try and get the same response you provided; but only searching for one of the values. I wanted to get correct input for one value before trying to add in more.

Edit 3: Was able to write the script with the assistance of a friend, thank you so much everyone for their input. I have attached the working script on here:

Option Explicit
Sub Autoformat()
Dim WSA As Worksheet
Dim Rng1 As Range
Dim Rng2 As Range
Dim lpRange As Range
Dim sArr As Variant
Set WSA = ActiveSheet
Dim i As Long

sArr = Array("Suppressed", "Other Response Categories", "Requires Challenge Response", "Tracking Links Clicked", "Link Name (HTML)")
Rows("6:9").Delete

With Application
  .Calculation = xlCalculationManual
  .ScreenUpdating = False
  .DisplayAlerts = False
  .EnableEvents = False
End With
For i = 0 To 3 Step 2
   Set lpRange = WSA.UsedRange
   Set Rng1 = lpRange.Find(What:=sArr(i), _
        LookAt:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        MatchCase:=False)
   Set Rng2 = lpRange.Find(What:=sArr(i + 1), _
        LookAt:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        MatchCase:=False)
  On Error Resume Next
  If Not Rng1 Is Nothing And Not Rng2 Is Nothing And Rng2.Row > Rng1.Row Then
    WSA.Rows(Rng1.Row & ":" & Rng2.Row - 1).Delete
  ElseIf Not Rng1 Is Nothing And Rng2 Is Nothing Then
    WSA.Rows(Rng1.Row).Delete
  End If
Next i
Set lpRange = WSA.UsedRange
Set Rng2 = lpRange.Find(What:=sArr(i), _
        LookAt:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        MatchCase:=False)

If Not Rng2 Is Nothing Then
 WSA.Rows(Rng2.Row & ":" & Rows.Count).Clear
End If

With Application
  .Calculation = xlCalculationAutomatic
  .ScreenUpdating = True
  .DisplayAlerts = True
  .EnableEvents = True
End With


End Sub
Sub ClearNames()
Dim n As Name
For Each n In ThisWorkbook.Names
 n.Delete
Next n

End Sub

Answer 1

The Macro Recorder is a great way of discovering the syntax of statements you do not know. However, the Macro Recorder does not know your objectives; it just records each of your actions. The result needs a lot of tidying up.

You must learn Excel VBA if you are going to post questions and expect to understand the answers. Search the web for "Excel VBA Tutorial". There are many to choose from so pick one that matches your learning style. I preferred to visit a large library and try out the VB Excel primers they had. I then bought the one I liked.

This is to give you are start at tidying up something created with the Macro Recorder.

I placed your key phrases in random cells down a worksheet and then searched for them in turn. The macro recorder's output was:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 07/05/2014 by Tony Dallimore
'
    Cells.Find(What:="Suppressed", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Cells.Find(What:="Other Response Categories", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Cells.Find(What:="Requires Challenge Response", After:=ActiveCell, LookIn _
        :=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Cells.Find(What:="Link Name (HTML)", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
End Sub

This code finds the four phrases but does not do anything else. This shows the syntax of the Find method. We need to tidy this code and save row numbers rather than activate (select) cells. Rather than try to explain each change I have created the code below from the code above. Study the differences and try to understand what I have done and why. Come back with questions if necessary but the more you can achieve on your own, the faster you will build up your skills.

Sub Demo()

  Dim Rng As Range
  Dim RowSupp As Long
  Dim RowOther As Long
  Dim RowReq As Long
  Dim RowLink As Long

  With Worksheets("Sheet1")

    Set Rng = .Cells.Find(What:="Suppressed", After:=.Cells(Rows.Count, Columns.Count), _
                          LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                          SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

    ' Note in the above statement I have replaced "ActiveCell" with ".Cells(Rows.Count, Columns.Count)"
    ' which is the bottom right cell.  Find does not look at the start cell, it wraps and starts
    ' searching from A1.  I have also replaced "xlPart" with "xlWhole".

    If Rng Is Nothing Then
      Call MsgBox("""Suppressed"" not found", vbOKOnly)
      Exit Sub
    End If

    RowSupp = Rng.Row

    Set Rng = .Cells.Find(What:="Other Response Categories", After:=.Cells(Rng.Row, Rng.Column), _
                          LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                          SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

    If Rng Is Nothing Then
      Call MsgBox("""Other Response Categories"" not found", vbOKOnly)
      Exit Sub
    End If

    RowOther = Rng.Row

    Set Rng = .Cells.Find(What:="Requires Challenge Response", After:=.Cells(Rng.Row, Rng.Column), _
                          LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                          SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

    If Rng Is Nothing Then
      Call MsgBox("""Requires Challenge Response"" not found", vbOKOnly)
      Exit Sub
    End If

    RowReq = Rng.Row

    Set Rng = .Cells.Find(What:="Link Name (HTML)", After:=.Cells(Rng.Row, Rng.Column), _
                          LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                          SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

    If Rng Is Nothing Then
      Call MsgBox("""Requires Challenge Response"" not found", vbOKOnly)
      Exit Sub
    End If

    RowLink = Rng.Row

  End With

  Debug.Print """Suppressed"" found on row " & RowSupp
  Debug.Print """Other Response Categories"" found on row " & RowOther
  Debug.Print """Requires Challenge Response"" found on row " & RowReq
  Debug.Print """Link Name (HTML)"" found on row " & RowLink

End Sub

The above macro has found the four rows and has proved it has found them by outputting their values to the Immediate Window.

For my dummy worksheet, the output is:

"Suppressed" found on row 6
"Other Response Categories" found on row 11
"Requires Challenge Response" found on row 16
"Link Name (HTML)" found on row 22

If I am unsure what I am doing, I always code this way. I identify step 1 and code a routine to achieve step 1. I then identify step 2 and update my code to achieve that as well.

If my data matched yours, you would want rows 6 to 10 deleted.

You have posted:

Rows("6:9").Select
Selection.Delete Shift:=xlUp

If we tidy that up we first get:

.Rows("6:9").Delete Shift:=xlUp

The next step is to replace the 6 and the 9 with the row numbers that macro Demo discovered:

.Rows(RowSupp & ":" & RowOther - 1).Delete Shift:=xlUp

Place this under RowOther = Rng.Row and run Demo again.

The first lot of rows are deleted.

Step 3 is to consider how to adjust the third Find statement. The current macro relies on RowOther not moving between Finds 2 and 3. But it has moved up by the number of lines deleted. You cannot use .Cells(Rng.Row, Rng.Column) as the start point for Find 3.

I leave you to think about where to start Find 3.