Copy, Paste Special Values

Hi,

Try this:

Sub a()
Dim rCell As Range
For Each rCell In Range(Range("F1"), Range("F1").SpecialCells(xlLastCell))
rCell.Value = rCell.Value
Next
End Sub

Note that this only works on the populated cells starting at row 1.

To do the whole column, replace

Range(Range("F1"), Range("F1").SpecialCells(xlLastCell))

with

Range("F:F")

Try

Dim rng As Range
Set rng = Range(Cells(1, 6), Cells(Rows.Count, 6).End(xlUp))
rng.Value = rng.Value