Last row of Filtered Data

[thardin]

How do I change this code so this Macro selects the last row of filtered data (offset by 3). right now it overrides data in a filtered range when I run it.

For example, if my data ranges from A1 to A50. How do I make it select A53, even if some of the last rows are hidden?

Sub Signoff()
'
' Signoff Macro
' Keyboard Shortcut: Ctrl+Shift+S


    Dim strFullDate As String

    strFullDate = Format(Date, "mm.dd.yy")
    
    Range("B" & Rows.Count).End(xlUp).Offset(3).Select
    ActiveCell.FormulaR1C1 = "Terah"
    Range("B" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveCell.FormulaR1C1 = "Completed " & strFullDate
  
End Sub

 

[Fluff]

How about

   Dim Rw As Long
   With ActiveSheet
      If .AutoFilterMode Then
         Rw = Split(.AutoFilter.Range.Address(1, 0), "$")(2)
      Else
         Rw = Range("B" & Rows.Count).End(xlUp).Row
      End If
      .Range("B" & Rw).Offset(3) = "Terah"
   End With

 

[thardin]

Well Done! Thanks

I like how you included the IF. That helps a lot.

However, you forgot the 2nd row, but I included it in.
Can you please explain that 4th row of code so I can understand?
Rw = Split(.AutoFilter.Range.Address(1, 0), "$")(2)

Sub test()

Dim Rw As Long
Dim strFullDate As String

strFullDate = Format(Now, "mm.dd.yy")

With ActiveSheet
If .AutoFilterMode Then
Rw = Split(.AutoFilter.Range.Address(1, 0), "$")(2)
Else
Rw = Range("B" & Rows.Count).End(xlUp).row
End If
.Range("B" & Rw).Offset(3) = "Terah"
.Range("B" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "Completed " & strFullDate
End With

End Sub

 

[Fluff]

However, you forgot the 2nd row

No I didn't, I just showed you how to do it.

This .AutoFilter.Range.Address(1, 0) will return the address of the filter range in the form of A$1:M$771 and the Split function will split that into an array based on the $
As split always returns a 0 based array, the 1st item (index 0) will be A & the 3rd item (index 2) will be 771 which is the last row