Count Filtered rows and display count

Question

I want to filter by three criteria, count the number of rows that are filtered, output that count in cell N2, and then delete the rows that were filtered. I'm not sure why my code below is not working.

Sheets("Sheet1").Range("B4").Select
Sheets("Sheet1").Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.AutoFilter Field:=8, Criteria1:=Array("A", "B", "C"), Operator:=xlFilterValues
Selection.Cell("N1").Select
ActiveCell.Value = Range(Cells(1, 1), Cells(Selection.SpecialCells(xlcelltypelast).Row, Selection.SpecialCells(xlCellTypeLastCell).Column)).Count
Selection.AutoFilter

Do you get any errors? What does your code actually do vs. what did you expect it to do? • 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). • Also neither `xlLastCell` nor `xlcelltypelast` do exist you probably meant `xlCellTypeLastCell` I recommend to activate `Option Explicit` to avoid such typos: In the VBA editor go to *Tools* › *Options* › *[Require Variable Declaration](https://www.excel-easy.com/vba/examples/option-explicit.html)*. — Pᴇʜ, Feb 14 '19 at 12:58
Thank you for the feedback. The error I get is on the activecell.value line. "Unable to get the specialcells property of the range class". I changed the xlcelltypelast to xlcelltypelastcell and it doesn't error out. However, it is displaying 1125, when in fact it should be 4 in this current instance. It is also putting it in cell O4 for some reason. — JTL27, Feb 14 '19 at 13:22

Pᴇʜ · Accepted Answer · 2019-02-14T14:03:01.783

Imagine this data

First of all you should avoid using Select in Excel VBA.
Also neither xlLastCell nor xlcelltypelast do exist you probably meant xlCellTypeLastCell I recommend to activate Option Explicit to avoid such typos: In the VBA editor go to Tools › Options › Require Variable Declaration.
Filtered data might not be a continuous range but divided in different areas.

So FilterRange.SpecialCells(xlCellTypeVisible).Rows.Count would give you only the row count of the first area. So you must loop through the areas For Each iArea In FilterRange.SpecialCells(xlCellTypeVisible).Areas and sum up the .Rows.Count to get their total.

Final result after delete:

Option Explicit

Sub FilterAndDelete()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    Dim FilterRange As Range
    Set FilterRange = ws.Range(ws.Range("B4"), ws.Range("B4").SpecialCells(xlCellTypeLastCell))

    FilterRange.AutoFilter Field:=8, Criteria1:=Array("A", "B", "C"), Operator:=xlFilterValues

    Dim RowCount As Long

    Dim iArea As Range
    For Each iArea In FilterRange.SpecialCells(xlCellTypeVisible).Areas
        RowCount = RowCount + iArea.Rows.Count
    Next iArea

    ws.Range("N2").Value = RowCount - 1 'subtract header

    'delete rows but keep header
    Dim RowsToDelete As Range
    On Error Resume Next 'next line throws error if filter is empty. Hide it.
    Set RowsToDelete = FilterRange.Resize(RowSize:=FilterRange.Rows.Count - 1).Offset(RowOffset:=1).SpecialCells(xlCellTypeVisible)
            'This Part FilterRange.Resize(RowSize:=FilterRange.Rows.Count - 1).Offset(RowOffset:=1) excludes the header from the FilterRange (we don't want to delete that).
    On Error GoTo 0 'always re-activate error reporting!
    If Not RowsToDelete Is Nothing Then
        RowsToDelete.EntireRow.Delete
    End If
    FilterRange.AutoFilter
End Sub

That works perfect! Thank you so much. Yes, I will read how to avoid using selects. — JTL27, Feb 14 '19 at 13:29
Actually, it does the count perfect, but it does not delete the rows out. Thoughts? — JTL27, Feb 14 '19 at 13:33
@JTL27 Strange I tested it. Any errors? Did you use my latest edit? — Pᴇʜ, Feb 14 '19 at 13:34
No errors. Yes, I did. It displays the count perfectly, but will not delete the lines for some reason. Thank you so much for your assistance on this. — JTL27, Feb 14 '19 at 13:59
Please try to replace it with `Set RowsToDelete = FilterRange.Resize(RowSize:=FilterRange.Rows.Count - 1).Offset(RowOffset:=1).SpecialCells(xlCellTypeVisible)` and try again. • If this doesn't help please comment out `On Error Resume Next` and tell which error you get. — Pᴇʜ, Feb 14 '19 at 14:02

Count Filtered rows and display count

1 Answers1