VBA Find/Delete Rows Based on Cell Value

[DixiePiper]

I have a pipeline maintained in a web application and need to export the data. A built-in export function will give me an Excel report, but the default format is borderline useless, and there is no way to customize it within the application. I am building out some VBA code to transform it into something I can actually use. The data is organized into several sections, and I need to delete rows between the sections to get one data set vs. 6 groups. Here is a link to a sample file: Pipeline Example

Some notes:

1. The section titles are always the same (Presolicitation, Active Pricing, etc.)
2. Each section title is always followed by the same row of column headers
3. There are always 2 blank lines below the last data row of the previous section and the next section's title.
4. The number of data rows in each section (e.g., Effort 1, Effort 2, etc.) will vary depending on the current workload.
5. I do not need to preserve the section titles as that data is in one of the report columns.

My goal is to keep the first section name and the column titles in the following row. I need to find each subsequent section title and delete the row below through 2 rows above until I have a single data set.

I'm working with Office 365 on a Windows 11 machine.

 

[HongRu]

Perhaps you could provide some examples of expected results.

 

[Alex Blakenburg]

I'm not sure why you want to keep the first section title given your point #5 but give this a try on a copy of your workbook,
At the moment it works on the ActiveSheet.

Sub RemoveRowsUsingFilter()

    Dim ws As Worksheet
    Dim LastRow As Long
    Dim HdgRow As Long
    Dim FltrRng As Range, FltrDataRng As Range
    
    Set ws = ActiveSheet
    With ws
        If .AutoFilterMode Then .AutoFilterMode = False
        LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        HdgRow = .Columns("A").Find(What:="Name", MatchCase:=False, SearchDirection:=xlNext).Row
        Set FltrRng = .Range(.Cells(HdgRow, "A"), .Cells(LastRow, "A"))
        Set FltrDataRng = FltrRng.Offset(1).Resize(FltrRng.Rows.Count - 1)
    End With
       
    FltrRng.AutoFilter Field:=1, Criteria2:="="
    FltrDataRng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
    
    FltrRng.AutoFilter Field:=1, Criteria1:="=Name"
    Dim DelRng As Range
    Set DelRng = Union(FltrDataRng.SpecialCells(xlCellTypeVisible), _
                       FltrDataRng.SpecialCells(xlCellTypeVisible).Offset(-1))
    ws.AutoFilterMode = False
    DelRng.EntireRow.Delete

End Sub

 

[DixiePiper]

Alex - that worked PERFECTLY! I'm trying to decode exactly what you did so I can do this on my own should a similar situation arise. Please correct me if I'm wrong:

1. The first thing is to find the first instance of "Name" in Col A to define the header row.
2. It looks like the next step takes the range from the header row (offset?) to the last row and filters out the data to get the blank rows.
3. The visible (blank) rows are then deleted, leaving just the section titles and section column labels.
4. The next bit finds the rows with "Name" in Col A, uses offset to include the section title, and then deletes those rows.

Hopefully, that captures, even roughly, what you did. Also, the point of keeping the first section title is to replace it with different text. It's easier (I think) to keep that row and change the cell value than to delete the row just to add it back in.

Lisa

 

[Alex Blakenburg]

1. Correct
2. Correct
3. Correct
4. Slight change in wording here.
   It filters for the Heading Rows using "Name".
   SpecialCells then gets the Visible rows. We also need the section Headings as well which are one row above that ie "Offset(-1)".
   Using Union Store both sets of rows in DelRng.
   The reason I stored the reference in DelRng before deleting is that the delete won't work with a mix of visible and hidden rows with the filter still turned on.
   So turn off the Autofilter making all rows visible.
   Then delete the rows using thee DelRng
   
   

 

[DixiePiper]

I appreciate the clarification. Again, thank you so much for taking the time to write not only the script but the additional information. It is greatly appreciated.

 

[Alex Blakenburg]

You're welcome. Glad we could help.