Visual Basic code to Select and Delete filtered rows of data-CodePudding

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

CodePudding user response：

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

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

CodePudding user response：

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 - can anyone help further? Thanks