Find 1st blank row to fill in when there is an active filter

[Shloime]

I'm using VBA code to fill data into a sheet that already contains data, so I need to find the first blank row.
these are the 2 methods use, I prefer the first because it checks all columns, The issue with both is, when i have a filter on the target sheet it will find the last visible cell the same as when i click ctrl + arrow up in the worksheet +1 will be a row hidden by the filter and the code would over write the data there.
Does anyone have a solution to handle this, or a different method to find the first blank row?

 Debug.Print Sheets("Sheet1").Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Row + 1
    Debug.Print Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1

 

[kvsrinivasamurthy]

Try this.

Sub Macro1()
Dim Frng, Clms&, Bclm&, T&, FBRo&
Clms = ActiveSheet.UsedRange.Rows.Count
For T = 1 To Clms
Set Frng = Rows(T).Find("*")
If Frng Is Nothing Then Bclm = Bclm + 1
Next T
FBRo = Clms - Bclm + 1
End Sub

 

[Alex Blakenburg]

UsedRange includes formatting. This won't impact the last row if the formatting is applied to the whole column but individual formatting will impact it.
If any row heights below your data have been changed that will impact it as well.

there are many blank rows in middle

Depending on what this means another option might be to do a similar thing using current region.
The below looks for the last visible cell in column A then applies CurrentRegion to that, which is the range in all directions from that cell until it hits a fully blank row or column
(ctrl+* or ctrl+A with the cell selected will mimic that behaviour)

Sub LastRowFilteredDataCurrentRange()

    With Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).CurrentRegion
        Debug.Print .Cells(1).Offset(.Rows.Count).Row
    End With

End Sub