VBA Macro that only Modifies Filtered/Visible Cells

[AppleZapple]

Hi VBA experts, I really need your help for this issue.

Say I have 3 columns: A, B, C
Name; Gender; Status (respectively)

I have written a separate filter code to show only all Gender=Male whom Status=Rejected. At this point, all unwanted rows have been filtered/hidden and the visible data will be showing all Males with Rejected statuses.

However, what I'm required to do is to add a "(Rejected)" in front of the name of all visible cells.

My current code is:

Dim LastRow as Long
Dim i As Long
LastRow = Range("$C" & Rows.Count).End(xlUp).Row
For i=2 To LastRow
If Range("$C" & i).Value = "Rejected" Then
Range("A" & i).Value = "(Rejected) " & Range("A" & i).Value
End If
Next i

This code works but it seems like it affects all cells (including the ones hidden) in which this case, all Females with Rejected statuses that falls under the loop would also get updated.

I'm looking for a solution and have tried ways to nest the code in a SpecialCells(xlCellTypeVisible) loop with no avail. Not sure whether am I heading to the right direction by programming the code to run this way.

All help from you pros are greatly appreciated. thanks.

 

[Akuini]

Try this:

Sub aTest()
Dim r As Range
For Each r In Range("A2:A" & Cells(Rows.count, "A").End(xlUp).row).SpecialCells(xlCellTypeVisible)
r.Value = "(Rejected) " & r.Value
Next
End Sub

 

[Michael M]

Maybe this

Sub MM1()
Dim LastRow As Long, r As Range, filter_rng As Range
Dim i As Long
LastRow = Range("$C" & Rows.Count).End(xlUp).Row
Set filter_rng = Range("A2:A" & LastRow).Rows.SpecialCells(xlCellTypeVisible).Rows
For Each r In filter_rng.SpecialCells(xlCellTypeVisible)
    If Range("C" & r.Row).Value = "Rejected" Then
Range("A" & r.Row).Value = "(Rejected) " & Range("A" & r.Row).Value
End If
Next r
End Sub

 

[AppleZapple]

Try this:

Sub aTest()
Dim r As Range
For Each r In Range("A2:A" & Cells(Rows.count, "A").End(xlUp).row).SpecialCells(xlCellTypeVisible)
r.Value = "(Rejected) " & r.Value
Next
End Sub

Thanks! Works to add the status for all visible cells.

 

[AppleZapple]

Maybe this

Sub MM1()
Dim LastRow As Long, r As Range, filter_rng As Range
Dim i As Long
LastRow = Range("$C" & Rows.Count).End(xlUp).Row
Set filter_rng = Range("A2:A" & LastRow).Rows.SpecialCells(xlCellTypeVisible).Rows
For Each r In filter_rng.SpecialCells(xlCellTypeVisible)
    If Range("C" & r.Row).Value = "Rejected" Then
Range("A" & r.Row).Value = "(Rejected) " & Range("A" & r.Row).Value
End If
Next r
End Sub

Worked like a charm! This code first test for the criteria before it works to add to visible cells only! Thank you for your great help!