Select first visible cell directly beneath the header of a filtered column

Untested but:

Sub Macro16()

    With ActiveSheet.Range("A1").CurrentRegion
        .AutoFilter field:=12, Criteria1:="Sheets"
        If .Columns(1).SpecialCells(xlCellTypeVisible).count > 1 Then
            With .Columns(10)
                .Resize(.rows.count - 1).offset(1).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=RIGHT(RC[1],3)"
            End With
        End If
    End With

End Sub

Sub FirstVisibleCell()
    With Worksheets("You Sheet Name").AutoFilter.Range
       Range("A" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
    End With
End Sub

I prefer non-destructive methods of determining whether there are visible cells to work with after a filtering operation. Since you are filling in column J with a formula, there is no guarantee that column J contains any values tat can be counted with the worksheet's SUBTOTAL function (SUBTOTAL does not count rows hidden by a filter) but the formula you are planning to populate into column J references column K so there must be something there.

Sub Macro16()
    With ActiveSheet
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, 1).CurrentRegion
            .Columns(12).AutoFilter Field:=1, Criteria1:="Sheets"
            With .Resize(.Rows.Count - 1, 1).Offset(1, 9)
                If CBool(Application.Subtotal(103, .Offset(0, 1))) Then
                    .SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=RIGHT(RC[1],3)"
                End If
            End With
            .Columns(12).AutoFilter Field:=1
        End With
    End With
End Sub

      Fill Formula to Visible cells

Tags:

Excel

Vba