Finding last row with data when filter is on

[Glove303]

If I want to find the last row with data in a column that has a filter, is there a way to do this (in vb) without having to expand all rows, i.e turn off the filter?

Because currently my code is much slower from having to constantly turn off and the filter.

The filter is run by code by the way, in the form of a loop. It is not an auto filter

 

[Peter_SSs]

I assume that you are looking for the last row, even if it is hidden. (I've used column B and I've assumed there is at least 1 item of data in the column.)

If so, try

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> LR1()<br>    <SPAN style="color:#00007F">Dim</SPAN> rCol <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> lRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rCol = Intersect(ActiveSheet.UsedRange, Columns("B"))<br>    lRow = rCol.Row + rCol.Rows.Count - 1<br>    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> Len(Range("B" & lRow).Value) = 0<br>        lRow = lRow - 1<br>    <SPAN style="color:#00007F">Loop</SPAN><br>    MsgBox lRow<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


If you want the last visible row with data then try


<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> LR2()<br>    <SPAN style="color:#00007F">Dim</SPAN> lastrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    lastrow = Range("B" & Rows.Count).End(xlUp).Row<br>    MsgBox lastrow<br>End <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Glove303]

Thanks Peter, that is great. Yes, the first example was what I wanted, as I want to find the data in all rows, not just the hidden ones.

Many thanks,

James.