Excel VBA - Using cell with specific text in row 1, look for next instance and delete all rows in between the two RRS feed

  • Question

  • Hello my fellow VBA enthusiasts.  I am trying to figure out the best way to structure a bit of code.  What I want to do is, as an example, the following:

    Row 1 (cell A) has the text "This is Legacy row One UPI blah blah blah blah"    Keywords here are "*Legacy*UPI*"

    So I need the information in the next row, which is

    Row 2 has the text "REGION: TC"

    I want to cycle through the proceeding rows for a cell with "*REGION*TC*".  If I find that phrase, delete all rows from Row 1 down to and including the row with the phrase.

    I'm thinking there may be some looping involved here but I have a tendency to over complicate things so I was wondering what might be an easier way?  Thank you everyone!!! :D


    Tuesday, November 3, 2015 2:54 PM

All replies

  • Does this do what you want? Please test on a copy of your worksheet!

    Sub DeleteRows()
        Dim cel As Range
        Set cel = Range("A:A").Find(What:="REGION*TC*", LookAt:=xlWhole, MatchCase:=False)
        If Not cel Is Nothing Then
            Range(Range("A1"), cel).EntireRow.Delete
        End If
    End Sub

    Regards, Hans Vogelaar (

    Tuesday, November 3, 2015 3:17 PM
  • Thank you for your suggestion.  Here is what I have so far, but I cannot get it to find the next cell with REGION in it:

    Sub cleanQualityData()
        Dim intMyVal As String
        Dim intMyVal2 As String
        Dim intMyVal2b As Variant
        Dim intMyVal3 As String
        Dim varRegion As String
        Dim lngLastRow As Long
        Dim strRowNo As Variant
        Dim strRowNo2 As Variant
        'Value to search for, change as required.
        intMyVal = "*LEGACY*UPI*"
        'Search Column A.
        lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
        'Starting cell is A1 (search through to last row in column A).
        For Each cell In Range("A1:A" & lngLastRow)
        'If the cell is equal to "*LEGACY*UPI*"
        If cell.Value2 Like intMyVal Then
            'get row number of current cell    (1)
            strRowNo = cell.Row
            'get text value of next cell below current     (REGION: TC)
            intMyVal2 = cell(strRowNo + 1, "A").Value
            'get row number of next cell below current     (2)
            intMyVal2b = cell(strRowNo + 1, "A").Row

            intMyVal3 = Range("A" & intMyVal2b + 1 & ":A" & lngLastRow).Find(What:="REGION ??:*", LookAt:=xlWhole, MatchCase:=False).Row
        End If
        Next cell
    End Sub


    Tuesday, November 3, 2015 6:13 PM
  • Try this version:

    Sub CleanData()
        Dim cel1 As Range
        Dim cel2 As Range
        Application.ScreenUpdating = False
            Set cel1 = Range("A:A").Find(What:="*LEGACY*UPI*", MatchCase:=False)
            If cel1 Is Nothing Then Exit Do
            Set cel2 = Range("A:A").Find(What:="REGION*TC*", MatchCase:=False, After:=cel1)
            If cel2 Is Nothing Then Exit Do
            Range(cel1, cel2).EntireRow.Delete
        Application.ScreenUpdating = True
    End Sub

    Regards, Hans Vogelaar (

    Tuesday, November 3, 2015 8:33 PM
  • Thank you for your help Hans :D


    Wednesday, November 4, 2015 5:23 PM