Excel VBA Fining Last Row of Filtered Range

Question

I am trying to find the number of rows of a filtered range on a sheet. LstRow2 is the variable I am trying to find. With this code I am getting the unfiltered number of rows.

   CSht.Range(CSht.Cells(1, 1), CSht.Cells(LstRow1, LstCol1)).AutoFilter Field:=2, Criteria1:="RA"
        With CSht
        LstRow2 = .UsedRange.SpecialCells(xlCellTypeLastCell).Row
        End With

Scott Holtzman · Accepted Answer · 2016-07-08T16:03:55.657

1

You'll need to work with the visible cells only, since it's filtered.

Try this:

 With CSht

     'load filter cells into Range object
     Dim rngFilter as Range
     Set rngFilter = Intersect(.UsedRange,.UsedRange.Offset(1)).SpecialCells(xlCellTypeVisible)

     'find the max number of elements split by $ in the range address
       'needs to be dynamic because of areas
     Dim lUpper as Long
     lUpper = UBound(Split(rngFilter.Address,"$"))

     'the last element will be the last row in the filtered range
       'the last number in the rngFilter.Address
     Dim LstRow2 as Long
     LstRow2 = Split(rngFilter.Address,"$")(lUpper)

End With

edited Jul 08 '16 at 16:03

answered Jul 08 '16 at 15:57

Scott Holtzman

27,099
5
37
72

Hi Scott. I realized that LastRow2 is coming out with an incorrect value. I am also not sure what lUpper is. Thanks for your help. – Liz Jul 11 '16 at 12:39
@Liz - `lUpper` is a variable to catch the highest element in the array when you split the formula up by `$`. It's needed because the amount of areas in the range can be arbitrary when filtering. The highest element will always have the last row of filtered data. When you say `LstRow2` is coming out with an incorrect value, what do you mean? – Scott Holtzman Jul 11 '16 at 14:09
I mean the value should be 3 but it is coming out as 5 which is the number of elements before the list is filtered. – Liz Jul 11 '16 at 14:22
@liz - so, you have 5 total rows, but the last filtered row is row 3? – Scott Holtzman Jul 11 '16 at 14:51
Unfiltered = 5 rows; filtered = 3 rows. I want LstRow2 to equal 3 because I have three rows now – Liz Jul 11 '16 at 14:53
@liz - are you sure you ran the code *exactly* as how I provided. I tested this fully and it worked for me. – Scott Holtzman Jul 11 '16 at 15:05
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/117001/discussion-between-liz-and-scott-holtzman). – Liz Jul 11 '16 at 15:18

score 0 · Answer 2 · answered Jul 08 '16 at 13:54

0

Why don't you replace this line

LstRow2 = .UsedRange.SpecialCells(xlCellTypeLastCell).Row

With

LstRow2 = .Cells(.rows.count, 1).end(xlup).row

answered Jul 08 '16 at 13:54

Sixthsense

1,927
2
16
38

this will not work as `.End(xlUp)` ignores filtered ranges. It works solely on the worksheet as a whole. – Scott Holtzman Jul 08 '16 at 16:07
This worked for me just now. I did a bunch of single digits in column 1 and filtered them so that something on row 5 would be the last row and rows 6-10 were hidden and lastrow ends up as 5. – Rodger Jul 08 '16 at 16:14

Gediminas · Answer 3 · 2020-03-03T08:22:17.313

There may be several areas after filtering so you need to use Areas. Great explanation here https://stackoverflow.com/a/17287558/3733214. This should work:

Dim Line as Range
For Each Line In CSht.UsedRange.SpecialCells(xlCellTypeVisible).Areas
  LstRow2 = LstRow2 + Line.Rows.Count
Next

Credits: https://www.mrexcel.com/board/threads/vba-code-to-count-visible-rows-after-autofiltering-a-table.602866/post-2988416

score 0 · Answer 4 · answered May 06 '20 at 15:06

0

This formula lastRow = Worksheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row has given me the the correct last visible row of a filtered range.

answered May 06 '20 at 15:06

XLmatters

376
1
16

score -1 · Answer 5 · answered Jul 08 '16 at 16:12

-1

Current region will do it for you in a single line

LastRow = sht.Range("A1").CurrentRegion.Rows.Count

answered Jul 08 '16 at 16:12

Rodger

845
9
21

1

This didn't work for me. I got the same number as before for row count – Liz Jul 08 '16 at 16:18
Yeah, I was afraid of that because I am getting the hoped for results on the other one that didn't work for you. Can you share the rest of the code or no? – Rodger Jul 08 '16 at 16:21
Or can you try a test sub that is really short on your data and take out all the other possibly influencing factors and see if one or both of the code snippets work for you that way? – Rodger Jul 08 '16 at 16:22

Excel VBA Fining Last Row of Filtered Range

5 Answers5