apply formula to one column within a set range (easy!)

[nowanda]

Hello All!

This is a simple question as I'm sure I'm missing something small ->

I need to make one column (text) uppercase. If I do the following I make all of the columns with text uppercase...

Sub MakeCaps()
For Each x In ActiveCell.CurrentRegion.Cells
x.Value = UCase(x.Value)
Next
End Sub

What's the syntax to include one column in the range to the end of the sheet? (i.e. column A within a sheet of 100 rows - I don't want to run the script to eternity - I'd like it to run to the last cell containing an entry!)

Thanks!

 

[hedrijw]

Here's a way:

Sub Up_Case()
Set my_col = Range([a1], [A65536].End(xlUp))
For Each x In my_col.Cells
Range(x.Address) = UCase(x)
Next
End Sub

This will work assuming it's column A. Oherwise, change [A1] and [A65336] in the Set statement

 

[nowanda]

Magic! Thanks!

Nowanda