after filtering, change every cell in column J

  • Thread starter Thread starter John Keith
  • Start date Start date
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)
 
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
 
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
Click to expand...


John Keith
(e-mail address removed)
 
Back
Top