Find Lastrow BUT AFTER a filter has run?

[FatalLordes]

Ok, I hope I can explain this well. I need to find the last true row on a sheet BUT my issue is I am also using a filter as part of the last section of the sheet. When I use LastRow, in theory it does work as it says the last row is the line that my filter formula starts... but of course the filter may find X amount of rows of data to show.

So what I need is to find the true last row of data AFTER the filter has run and populated rows.

Does that make sense? Is that even possible?

 

[bebo021999]

Could you send image of BEFORE filter and AFTER filter, with desired last row?

 

[FatalLordes]

Could you send image of BEFORE filter and AFTER filter, with desired last row?

Not sure what you mean. Basically the "lastrow" it thinks is on the sheet has the formula below in it, which, when runs, could find X rows of data to add or could find no rows of data to add:

=FILTER(Medical!B2:F999,Medical!A2:A999=B3,"")

I need to insert an image after it has found all/any medical data which would be the lastrow but AFTER it has run the filter, not the row that has the filter formula which is what it keeps telling me. For example, below is the sheet. What I want it to do is when the Show Scans button is pressed (because I don't know how to make it work automatically without the button), it needs to go to the true last row, not just A23, which happens to be the row with the FILTER formula in it. The listing may have X amount medical data to display. It is after all/any medical data that it then needs to include the pictures.

 

[bebo021999]

Sorry I do not have Ex365 then I can not test with FILTER function.
Maybe someone will jump in and give a hand.

 

[RoryA]

You haven't said what you are using to get LastRow (that's just a variable name) but if it's something like End(xlUp).Row in your code, that will reflect the correct row if the FILTER formula returns data.

 

[FatalLordes]

You haven't said what you are using to get LastRow (that's just a variable name) but if it's something like End(xlUp).Row in your code, that will reflect the correct row if the FILTER formula returns data.

Sorry, I didn't realise it made a difference. I'm very new to Excel and VBA. This is the code I'm using to find the last row (and I popped in msgbox so I could see what it found):

With Sheets("Report")
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
Lastrow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Else
Lastrow = 1
End If
MsgBox Lastrow
End With

 

[Skyybot]

Does this work?

Sub getLastRow()
Dim lRow as Long, ws as Worksheet
Set ws = ThisWorkbook.Sheets("Report")
lRow = ws.UsedRange.Rows.Row
MsgBox lRow & " is the last row in 'Report' Worksheet", vbOKOnly + vbInformation, "Last Row"
End Sub

 

[Alex Blakenburg]

That opens up a can of worms. "Range.Find" using xlFormulas is finding the formula but not the spilled data.
Changing it to xlValues finds the last row in the spilled range but does not find the formula cell if the formula returns "".

The safest option is the one suggested by Rory ie replace your find lines with:
Note: assumes your filter formula is in column A

            lastRow = .Cells(Rows.Count, "A").End(xlUp).Row

PS: Unfortunately skybot's suggestion will always return the "first" row number of the used range not the last.

 

[FatalLordes]

Thanks everyone! I went with Rory's suggestion and change my coding to the below and it works PERFECTLY! Thanks so much everyone! I love this forum!

With Sheets("Report")
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Else
Lastrow = 1
End If
MsgBox Lastrow
End With