Autofilter and Copy and Paste

[Guest]

I have a table of data in Sheet("Stepdown"). It has already been
autofiltered. I want to change all the autofiltered data in column T to "a".
Here is what I have (but it changes the filtered and autofiltered data to
"a").

Sheets("Stepdown").Range("T1").Select
Selection.End(xlDown).Select
ActiveCell.FormulaR1C1 = "a"
ActiveCell.Copy

Range(Selection, Selection.End(xlUp)).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

 

[Jim Rech]

I think all you want to do is this:

Sub Better()
Range("T2", Range("T2").End(xlDown)) _
.SpecialCells(xlCellTypeVisible).Value = "a"
End Sub

No need to select, copy, paste, etc. This assumes thought that sheet
Stepdown is active. If it's not then:

Sub Better2()
With Worksheets("SetDown")
.Range("T2", .Range("T2").End(xlDown)) _
.SpecialCells(xlCellTypeVisible).Value = "a"
End With
End Sub

--
Jim Rech
Excel MVP
|I have a table of data in Sheet("Stepdown"). It has already been
| autofiltered. I want to change all the autofiltered data in column T to
"a".
| Here is what I have (but it changes the filtered and autofiltered data to
| "a").
|
| Sheets("Stepdown").Range("T1").Select
| Selection.End(xlDown).Select
| ActiveCell.FormulaR1C1 = "a"
| ActiveCell.Copy
|
| Range(Selection, Selection.End(xlUp)).Select
| Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
| False, Transpose:=False
|
| --
| Thanks
| Shawn