Macros acting on a filtered list

[Jay Kay]

I have a filtered list and want to enter "SOLD" in column A for only the rows
that meet the filter criteria. I am having difficulty acting only on the
visible cells. (I am creating the macros by recording my keystrokes, not
writing VB code). Whether I enter the value in the first row and copy it
down or enter the value in each cell, and press <down arrow> to move to the
next cell, the macro records specific cell addresses. If I try relative
addresses, it records that specific number of rows to move down.

Can someone provide some suggestions, or perhaps a bit of code that sets the
range to the visible cells in Col. A that I can paste over the code that
indicates specific cells?

Thanks in advance for your assistance.

Regards

 

[Gary''s Student]

If AutoFilter hides rows then:

Sub sold()
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1

For i = 1 To nLastRow
If Cells(i, "A").EntireRow.Hidden Then
Else
Cells(i, "A").Value = "SOLD"
End If
Next
End Sub

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim VRng As Range

With Worksheets("sheet1").AutoFilter.Range
If .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then
'nothing but headers are visible
MsgBox "nothing but headers"
Else
Set VRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
VRng.Value = "SOLD"
End If
End With

End Sub

 

[Jay Kay]

Perfect. Thanks.

If AutoFilter hides rows then:

Sub sold()
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1

For i = 1 To nLastRow
If Cells(i, "A").EntireRow.Hidden Then
Else
Cells(i, "A").Value = "SOLD"
End If
Next
End Sub