after filtering, change every cell in column J

[John Keith]

After autofiltering a range of data I next want to change the value of
column J of every row found to "1". What is the mechanism for
specifying the sorted range and changing the value?


John Keith
(e-mail address removed)

 

[Bernie Deitrick]

If you need to apply the filter:

Sub TryNow()
Dim myR As Range
Set myR = ActiveCell.CurrentRegion
'Apply your filter
myR.AutoFilter Field:=7, Criteria1:=">=65", Operator:=xlAnd
Intersect(myR, Range("J2:J" & Rows.Count)).SpecialCells(xlCellTypeVisible).Value = 1
myR.AutoFilter
End Sub

Otherwise, if the filter is applied already

Sub SetJ()

Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("J2:J" & Rows.Count)) _
.SpecialCells(xlCellTypeVisible).Value = 1

End Sub

 

[John Keith]

Bernie,

Thank you, I'll try it out this weekend.

Sub SetJ()

Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("J2:J" & Rows.Count)) _
.SpecialCells(xlCellTypeVisible).Value = 1

End Sub

John Keith
(e-mail address removed)