 # Number Of Rows In A Range After Filter

• I have applied a filter to my worksheet and would like to know if there is a way to get the number of rows included in the resulting filtered data.

Code
``````Worksheets("All Work").Select
iCount = GetEnd(Worksheets("All Work"))
Set Cell1 = Cells(2, 1)
Set Cell2 = Cells(iCount, 6)
Worksheets("All Work").Range(Cell1, Cell2).AutoFilter Field:=7, Criteria1:="Unassigned"``````

Now that I have it filtered I need to know how many rows are there with data. I'll then set up a loop to read the data from each row. I can't use my getend function because it will still end up with 93 rows and the number of unassigned is about 23.

Thanks,
Scott
If it looks impossible, it probably is.

• Re: Number Of Rows In A Range After Filter

Hi,

Will this help?

Code
``icount2 = Range(Cell1, Cell2).SpecialCells(xlVisible).Cells.Count``
• Re: Number Of Rows In A Range After Filter

Quote from Scottintexas

I have applied a filter to my worksheet and would like to know if there is a way to get the number of rows included in the resulting filtered data.

Code
``````Worksheets("All Work").Select
iCount = GetEnd(Worksheets("All Work"))
Set Cell1 = Cells(2, 1)
Set Cell2 = Cells(iCount, 6)
Worksheets("All Work").Range(Cell1, Cell2).AutoFilter Field:=7, Criteria1:="Unassigned"``````

Now that I have it filtered I need to know how many rows are there with data.

You need to count visible Rows of your range? Then:

Code
``lngVisibleRowsCount = Worksheets("All Work").Range(Cell1,Cells(icount,1)).SpecialCells(xlCellTypeVisible).Cells.Count``
• Re: Number Of Rows In A Range After Filter

Quote from the Okk

If need to count visible Rows of your range, Then:

Code
``lngVisibleRowsCount = Worksheets("All Work").Range(Cell1,Cell(icount,1)).SpecialCells(xlCellTypeVisible).Cells.Count``

Cell with an s of course. • Re: Number Of Rows In A Range After Filter

That was what I needed. The Row Count worked great. Can this be used to count the rows used in a worksheet without the filter?

Thanks,
Scott
If it looks impossible, it probably is.

• Re: Number Of Rows In A Range After Filter

Finally, is there a way to exclude the header row? I dont want to keep pasting that row into the "Report" sheet.

Thanks,
Scott
If it looks impossible, it probably is.

• Re: Number Of Rows In A Range After Filter

Does this help to include the header?

Code
``icount2 = Worksheets("All Work").Range(cells(3,1),Cells(icount,1)).SpecialCells(xlCellTypeVisible).Cells.Count``
• Re: Number Of Rows In A Range After Filter

This code will copy the filtered rows without the Headers to the report sheet, starting at the next empty cell in column A. You might find using [pt]*[/pt] more efficient though.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!