Best way to determine lastrow - AFTER executing a Filter (1 Viewer)

[Isaac]

Function ExistsInRCP(str1ID As String, str2Id As String) As Boolean
Dim ws As Worksheet, lastrow As Long, rng As Range
Set ws = ThisWorkbook.Worksheets("RCP")
lastrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
ws.ListObjects("ListObjectName").Range.AutoFilter _
    Field:=3, Criteria1:= _
    str1ID & "_" & str2Id

Note how I get the lastrow in the sheet, a common method. (I've also seen some other methods, utilizing UsedRange or SpecialCells, but have been uncertain as to their advantages/disadvantages).

Let's say I execute that code and it applies a filter. Now, I want to find out whether "some rows" or "zero rows" were left, after applying the filter. It will be very possible when this function is called, that zero rows are returned.

What do you think is the BEST way to see if zero rows are returned? Just to use lastrow method again, and see if it goes all the way up to row 1?
Is there maybe another way I don't know that is better.

By the way, this is a table that was created by making and running a Data Connection to a Sharepoint list.

 

[Isaac]

Well, I can partly answer my own question with more experimentation now done.

the "lastrow" method (xlup) isn't accurate once the autofilter has been applied. if the autofilter is applied, and results in ZERO rows, then going to cell 1 million and doing an xlUp will first take you to the bottom row of the original range/table ....and only if you arrow up once more are you then in row 1. But I suppose I can work with that.

 

[Gasman]

Just guessing here, but if after a filter, I'd try the UsedRange option?

Plenty of links in Google, here is one, which reckons to use SpecialCells ?
https://answers.microsoft.com/en-us...red-data/749aa681-955e-47f2-9dcb-c1582e4460d5

 

[Isaac]

Thanks!

 

[pbaldy]

Shot in the dark because I've never used Excel filtering, but does this method from one of my apps work?

lngLastRow = xl.Cells.SpecialCells(11).Row

 

[pbaldy]

Geez, I guess I took a long time to find that. Sorry!

 

[Isaac]

Geez, I guess I took a long time to find that. Sorry!

That does seem to work, thanks Paul, this is very helpful!

 

[pbaldy]

That does seem to work, thanks Paul, this is very helpful!

Glad it helped Isaac. I use that when I'm pushing data into Excel with automation and I want to add totals or whatever at the bottom.

 

[Gasman]

For my benefit please?, what does the 11 represent?

 

[Minty]

For my benefit please?, what does the 11 represent?

It's a number bigger than 10 but smaller than 12...

Seriously though, It's the enumeration of the Last Row Used in a Range - from here https://docs.microsoft.com/en-us/office/vba/api/excel.xlcelltype

 

[pbaldy]

For my benefit please?, what does the 11 represent?

XlCellType Enum (Microsoft.Office.Interop.Excel)

Specifies the type of cells.

docs.microsoft.com

 

[Gasman]

XlCellType Enum (Microsoft.Office.Interop.Excel)

Specifies the type of cells.

docs.microsoft.com

Thank you Paul,
Minty pointed me to something similar as well.