CODEWITHSUNDEEP
excelvba
Jordyb
Jordyb

Reputation: 13

Deleting a specific range of rows with VBA

I'm very new to VBA so this might be a basic question..

Every week i'm exporting a file with all kinds of data. Within this file I have to delete the same range of rows every time. I could easily automate this by defining a range based on cell positions, but this range starts sometimes not on the same row. However, the range starts with the same value and ends with the same value every time.

Is there any chance I can delete automatically all the rows within the range from begin to bottom? Without needing to specify a range based on cell positions?

Upvotes: 1

Views: 2178

Answers (1)

QHarr
QHarr

Reputation: 84465

And this is what that might look like for you

Option Explicit
Sub TargetRows()

    Dim wb As Workbook
    Dim wsTarget As Worksheet
    Dim startCell As Range
    Dim endCell As Range
    Dim startMatch As String
    Dim endMatch As String

    startMatch = "Artikelgroep: Promotional material"
    endMatch = "Artikelgroep: (totaal)"

    Set wsTarget = ThisWorkbook.Worksheets("Sheet2") 'change as required
    Set startCell = wsTarget.Columns(1).EntireColumn.Find(what:=startMatch, LookIn:=xlValues, lookat:=xlPart)
    Set endCell = wsTarget.Columns(1).EntireColumn.Find(what:=endMatch, LookIn:=xlValues, lookat:=xlPart)

    Dim deleteRange As Range

    If Not startCell Is Nothing And Not endCell Is Nothing And startCell.Row <= endCell.Row Then
        Set deleteRange = wsTarget.Range("A" & startCell.Row & ":A" & endCell.Row)
    Else
        Debug.Print "1 or both values not found or end text found before start text."
    End If

    If Not deleteRange Is Nothing Then deleteRange.EntireRow.Delete
End Sub

Reference:

Excel VBA Find Method for specific column (@shai rado)

Upvotes: 1

Related Questions