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. Stop using .Select and Selection it makes your code slow and unreliable. — Pᴇʜ
– Pᴇʜ, Commented Mar 8, 2022 at 13:16
Can you please explain what you expect your code to do? It is not clear. — Pᴇʜ
– Pᴇʜ, Commented Mar 8, 2022 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
– Laura Pilkington, Commented Mar 8, 2022 at 14:55

Pᴇʜ · Accepted Answer · 2022-03-08 15:08:00Z

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

Manny · Accepted Answer · 2022-03-08 13:12:31Z

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 8, 2022 at 13:12

Manny

5091 gold badge3 silver badges5 bronze badges

1 Comment

Pᴇʜ Over a year ago

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.

Collectives™ on Stack Overflow

Visual Basic code to Select and Delete filtered rows of data

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related