CODEWITHSUNDEEP
excelvba
TA Arjun
TA Arjun

Reputation: 25

Delete the row below a specific text

I am trying to delete a row present below when the cell value in column A has a specific text.

Sub DeleteRowWithContentsGuidelines()
    Dim c As Range
    Dim SrchRng
    Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A65536").End(xlUp))
    Do
        Set c = SrchRng.Find("Chart only", LookIn:=xlValues)
        If Not c Is Nothing Then c.EntireRow.Delete
    Loop While Not c Is Nothing
End Sub

The row above deletes the row which has "Chart only" in the text but I want to delete the row below but not the exact row. is this possible?

Upvotes: 0

Views: 62

Answers (2)

Dave
Dave

Reputation: 4356

The .Find returns a range object, so you can just use something like the following to delete the row below the found text:

Sub DeleteRowWithContentsGuidelines()
    Dim c As Range
    Dim SrchRng
    Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A" & Activesheet.Rows.Count).End(xlUp))
    Set c = SrchRng.Find("Chart only", LookIn:=xlValues)
    If c is Nothing then Exit Sub
    dim firstAddress : firstAddress = c.Address
    Do
        If Not c Is Nothing Then c.Offset(1, 0).EntireRow.Delete
        Set c = SrchRng.FindNext(c)
        If c is Nothing then Exit Sub
    Loop While c.Address <> firstAddress
End Sub

Upvotes: 2

SJR
SJR

Reputation: 23081

Sub DeleteRowWithContentsGuidelines()
    Dim c As Range, rDel As Range, i As Long
    Dim SrchRng As Range, s1 As String

    Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A" & Rows.Count).End(xlUp))
    s1 = "Chart only"

    With SrchRng
        Set c = .Cells(1)
        For i = 1 To WorksheetFunction.CountIf(.Cells, s1)
            Set c = .Find(What:=s1, After:=c, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
            If Not c Is Nothing Then
                If rDel Is Nothing Then
                    Set rDel = c.Offset(1)
                Else
                    Set rDel = Union(rDel, c.Offset(1))
                End If
            End If
        Next i
        rDel.EntireRow.Delete
    End With
End Sub

Upvotes: 2

Related Questions