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


    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?


    I really appreaciate your help.

    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


    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!