CODEWITHSUNDEEP
excelvba
Laura Pilkington
Laura Pilkington

Reputation: 41

Visual Basic code to Select and Delete filtered rows of data

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.

Upvotes: 2

Views: 2145

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57683

  1. Filter your data
  2. Don't use .Select
  3. Exclude the header row if there is one
  4. Get all visible cells of the UsedRange (without the header)
  5. Delete them
  6. 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

Upvotes: 1

Manny
Manny

Reputation: 509

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

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

Upvotes: 0

Related Questions