Deleting a specific range of rows with VBA

Question

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?

Are those start and stop values repeated elsewhere within the same column? If not you could use Find to locate them and those cell references to set up your range for deletion. Or even, if they are the first match and last match respectively. — QHarr
– QHarr, Commented Mar 14, 2018 at 9:20
Can you show some data and any attempts you have made at coding so far? Text can be matched in lots of ways. Is it part of a larger bit of text in a cell? — QHarr
– QHarr, Commented Mar 14, 2018 at 9:22
Range starts with: "Artikelgroep: Promotional material" Within the range: "1234ART.NR PRODUCT NAME" Range ends with: "Artikelgroep: (totaal) " Within this range there are +- 100 rows with all different values and placed every week on another row. — Jordyb
– Jordyb, Commented Mar 14, 2018 at 9:29
is "Artikelgroep: Promotional material" within one cell by itself? — QHarr
– QHarr, Commented Mar 14, 2018 at 9:30

QHarr · Accepted Answer · 2018-03-14 10:05:28Z

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)

Collectives™ on Stack Overflow

Deleting a specific range of rows with VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related