Friday 30 September 2011

Cell function to entire column (Trim / Upper / Lower....)

The below code will apply the cell level function to the entire column.


Dim r As Range
    Set r = Intersect(ShtTemp.Columns(4), ShtTemp.UsedRange)
    r.Value = Evaluate("IF(ROW(" & r.Address & "),IF(" & r.Address & "<>"""",UPPER(" & r.Address & "),""""))")
    r.Value = Evaluate("IF(ROW(" & r.Address & "),IF(" & r.Address & "<>"""",TRIM(" & r.Address & "),""""))")