Help! Select blank cell in a filtered list

Originally Posted by jaslake

Hi yoko21

Play with this

Option Explicit

Sub SubTot()

    Dim LR As Long, LR1 As Long
    With Sheets("Sheet1")
        
        'Find Last Row in Column A With Data (plus 1)
        LR1 = .Range("A" & .Rows.Count).End(xlUp).Row + 1

        'Find True Last Row With Data (plus 3)
        LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row + 3
        
        'Clear Data from Column B below LR1 (the Old Formula)
        .Range("B" & LR1 & ":B" & LR).ClearContents
        
        'Find the New True Last Row (plus 3)
        LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious).Row + 3
        
        'Put this Formula in that Cell
        .Range("B" & LR).Formula = "=SUBTOTAL(109,B2:B" & LR - 3 & ")"
        
    End With
End Sub

Hi John,

I tested your code in a 20 client list and noticed the following:
Without filtering - It worked perfectly. The computation was made 3 cells below the last client in the list.
Filtered. Only including the clients from 1-10 and 15-20. - The computation was made 2 cells below the last client in the list.
Filtered. Only including clients from 1-15. No computation was made and deleted the last to data in column B.