Visual Basic code to Select and Delete filtered rows of data

Question

I have created a macro in an Excel workbook and pulled this code from VBA. The line Rows("45:45").Select does not work well as the filtered rows will vary each time I run the report. I am total novice when it comes to VBA so any help would be greatly appreciated!

Sheets("Sheet1").Select
ActiveSheet.Range("$A:$AQ").AutoFilter Field:=1, Criteria1:=Array("Chloe", "Bob", "GBUK", "Shape", "Lifestyle", "MYP", _
    "MYP Aus", "MYP In", "MYP Retail", "MYV", "MYV Retail"), Operator:=xlFilterValues
Rows("45:45").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete
ActiveSheet.Range("$A:$AQ").AutoFilter Field:=1

Thanks Laura

EDITED:

I have edited the code as follows;

Sheets("Sheet1").Select
ActiveSheet.Range("$A:$AQ").AutoFilter Field:=1, Criteria1:=Array("Chloe", "Bob", "GBUK", "Shape", "Lifestyle", "MYP", _
    "MYP Aus", "MYP In", "MYP Retail", "MYV", "MYV Retail"), Operator:=xlFilterValues
Rows(rowVariable).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete
ActiveSheet.Range("$A:$AQ").AutoFilter Field:=1

I am receiving a runtime error when I run the macro.

I have also removed .Select but I receive a Compile Error: invalid use of property.

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Stop using `.Select` and `Selection` it makes your code slow and unreliable. — Pᴇʜ, Mar 08 '22 at 13:16
Can you please explain what you expect your code to do? It is not clear. — Pᴇʜ, Mar 08 '22 at 14:44
I am trying to filter the data to remove rows that belong to a set criteria. I have built the macro by recording the macro in Excel and performing the steps. The next time I run the report, the rows will be different, which is why I need to change the code to reflect that but I don't know how to. — Laura Pilkington, Mar 08 '22 at 14:55

score 1 · Answer 1 · answered Mar 08 '22 at 15:08

Filter your data
Don't use .Select
Exclude the header row if there is one
Get all visible cells of the UsedRange (without the header)
Delete them
Remove the filter

And you end up with something like

Option Explicit

Public Sub DeleteFilteredData()
    Dim ws As Worksheet  ' define the sheet you want to work with
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Filter
    ws.Range("$A:$AQ").AutoFilter Field:=1, Criteria1:=Array("Chloe", "Bob", "GBUK", "Shape", "Lifestyle", "MYP", _
        "MYP Aus", "MYP In", "MYP Retail", "MYV", "MYV Retail"), Operator:=xlFilterValues
    
    ' get filtered data without heading
    Dim FilteredRows As Range
    On Error Resume Next  ' avoid an error message if no rows were filtered
    Set FilteredRows = ws.UsedRange.Resize(RowSize:=ws.UsedRange.Rows.Count - 1).Offset(RowOffset:=1).SpecialCells(xlCellTypeVisible)
    ' if you don't have a header row use
    'Set FilteredRows = ws.UsedRange.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0  ' re-activate error reporting !!!
    
    If Not FilteredRows Is Nothing Then
        FilteredRows.EntireRow.Delete
    Else
        MsgBox "Nothing to delete", vbInformation
    End If
    
    ws.Range("$A:$AQ").AutoFilter Field:=1
End Sub

score 0 · Answer 2 · answered Mar 08 '22 at 13:12

0

Rows("45:45").Select can be changed to Rows(rowVariable & ":" & rowVariable)

Where row variable is a variable which can change at run time.

answered Mar 08 '22 at 13:12

Manny

509
1
3
5

2

If start row and end row is the same as in "45:45" you can just write `Rows(45)` or `Rows(rowVariable)`. No need for `rowVariable & ":" & rowVariable`. – Pᴇʜ Mar 08 '22 at 13:15

Visual Basic code to Select and Delete filtered rows of data

2 Answers2