Delete Filtered Rows

Question

Overview:

I have 2 macros, one filters the data and the second deletes the visible rows below the header rows (header rows 1-12).

2 Questions:

1) How do I best combine these into a single macro? 2) How do I get the second one to work properly?

I receive the

Run Time Error 1004: Method 'Range' of object '_worksheet' failed

on the Delete() macro line:

Set rng = .Range("A12:A" & LastRow).SpecialCells(xlCellTypeVisible)

I have also tried:

Set rng = .Range("A12:A" & LastRow).SpecialCells(xlCellTypeVisible).cells

Sub Filter()
'filter and delete rows that have AW as FALSE

For Each sht In ThisWorkbook.Worksheets
    sht.Range("A12:AW12").autofilter Field:=49, Criteria1:="FALSE"
Next sht

End Sub


Sub Delete()

Dim sht As Worksheet, rng As Range, lastRow As Long
Set sht = Worksheets("Sheet1")

With sht
    lastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = .Range("A12:A" & LastRow).SpecialCells(xlCellTypeVisible)
    rng.EntireRow.Delete
    .AutoFilterMode = False
End With

End Sub

Add `Option Explicit` to the top of the module: `lastRow` <> `LstRw`. Even better, go to *Tools* > *Options* and check *Require Variable Declaration* to have it automatically added going forward. — BigBen, Nov 21 '19 at 14:51
Something minor: Within your `With` statement you can use: `lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row` — JvdV, Nov 21 '19 at 14:54
To combine, you can just copy the code from `Delete` UDF (**Hint:** it is not recomended to use reserved words as your UDF name or as variable names) within the `For Each sht..` loop. Don't copy `With sh` statement as you can use `sht` as your sheet. Have you checked the value of `rng` after its assigned a range? — Zac, Nov 21 '19 at 14:55
Furthermore, I learned something new myself recently. When you apply an `AutoFilter`, there is a secret named range created which will refer to the range that has been filtered. This might mean you don't even need to get the last row. You just need to refer to this named range. — JvdV, Nov 21 '19 at 14:56
@JvdV does that reference your `AutoFilter` question from a couple days back? — BigBen, Nov 21 '19 at 14:56
@BigBen, yes it does. I'm quite intregued by this secret named range. — JvdV, Nov 21 '19 at 14:57
@JvdV - and I'm already reading up about it on that question. Fascinating! — BigBen, Nov 21 '19 at 14:59
@JvdV.. secret named range.. can you point me to your post? I would love to readup on it — Zac, Nov 21 '19 at 14:59
@Zac, [here](https://stackoverflow.com/q/58889516/9758194) you go. — JvdV, Nov 21 '19 at 15:00

Mr.Riply · Answer 1 · 2019-11-21T17:36:49.173

0

Sub Filter()
    'filter and delete rows that have AW as FALSE

    Dim rng As Range

    For Each sht In ThisWorkbook.Worksheets

        LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

        sht.Range("A1:O1").AutoFilter Field:=9, Criteria1:="FALSE"

        sht.Range("A2:A" & LastRow).Delete

        If sht.AutoFilterMode Then
            sht.AutoFilterMode = False
        End If

    Next sht
End Sub

edited Nov 21 '19 at 17:36

answered Nov 21 '19 at 15:10

Mr.Riply

825
1
12
34

thanks, I am still getting a Compile Error: Invalid or Unqualified Reference with .Range. Thoughts? – Ryan S Nov 21 '19 at 16:45
I edited the post, this works for me, just adjust the cell references – Mr.Riply Nov 21 '19 at 17:37

score 0 · Accepted Answer · answered Nov 21 '19 at 17:37

This is a working code for anyone that might find this going forward.

Sub Filter23()
    'filter and delete rows that have AW as FALSE (working)
    Dim sht As Worksheet, rng As Range, lastRow As Long

    For Each sht In ThisWorkbook.Worksheets
        sht.Range("A12:AW12").autofilter Field:=49, Criteria1:="FALSE"
       With sht
        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set rng = sht.Range("A12:A" & lastRow).SpecialCells(xlCellTypeVisible)
        rng.EntireRow.Delete
       End With

    Next sht
End Sub

Delete Filtered Rows

2 Answers2