Select/fill visible cells – Online Training

Tested in Excel 365 (16.8730.2046) 64-bit

Public Sub WriteVisibleCells(TableRange As String, Optional RangeKey As Integer = 1)
Dim Rng As Range
Dim RowCount, LastCol, LastRow
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = Range("A" & rows.Count).End(xlUp).Row
' add some filter
ActiveSheet.Range(TableRange).AutoFilter Field:=2, Criteria1:="Sold"
ActiveSheet.Range(TableRange).AutoFilter Field:=3, Criteria1:="<>Sold"
Set Rng = ActiveSheet.AutoFilter.Range ' get the range of the filtered data
RowCount = Rng.Columns(RangeKey).SpecialCells(xlCellTypeVisible).Count - 1
If RowCount > 0 Then
fvr = rows("2:" & LastRow).SpecialCells(xlCellTypeVisible).Row ' get the first visible row
lvr = Range("A" & rows.Count).End(xlUp).Row ' get the last visible row
Cells(fvr, LastCol + 1).Select
ActiveCell = "Sold" ' add comment
If RowCount > 1 Then ' if more than 1 row you can use fill down*
Range(Cells(fvr, LastCol + 1), Cells(lvr, LastCol + 1)).SpecialCells(xlVisible).Select ' select the visible cells
Selection.FillDown
End If
End If
Range("a1").Select
ActiveSheet.ShowAllData
'* fill down doesn't work smooth on large file, let's say >1500 lines. Consider using FOR
End Sub
' use like this:
Sub main()
Call WriteVisibleCells("A1:D13")
End Sub

You can use the data sample below to test and modify the code: