Change all text in a column to uppercase

[Ken Loomis]

Is there a way to change all text in a column to upper case.

I tried this:

Worksheets("Enter Here").Range("B:B").Value = _
UCase(Worksheets("Enter Here").Range("B:B").Value)


But, as I am sure most of you here can tell, that did not work.

Is it because I am not accesing the column correctly, or do I just need to
use UCase on each individual cell?

Thanks,
Ken Loomis

 

[Norman Jones]

Hi Ken,

Try:

Sub Tester()
Dim rng As Range, rCell As Range

On Error Resume Next
Set rng = Intersect(Columns(2), ActiveSheet.UsedRange)
On Error Resume Next
For Each rCell In rng.SpecialCells(xlCellTypeConstants, 2)

rCell.Value = UCase(myCell)
Next rCell
On Error GoTo 0

End Sub

 

[Ken Loomis]

Thanks, Norman.

As usual, your suggestion worked.

I did have to change:

rCell.Value = UCase(myCell)

to:

rCell.Value = UCase(rCell)

but at least I am starting to be able to figure stuff like that out.

This sub requires the sheet "Enter Here" to be active.

Is there a way to do this without activating that sheet first?

Thanks,
Ken Loomis

 

[Norman Jones]

Hi Ken,

I changed variable names and missed one. Fortunately you spotted it!

 

[Ken Loomis]

I think I figured out how to do that without activating the sheet.

I changed:

Set rng = Intersect(Columns(2), ActiveSheet.UsedRange)

to:

Set rng = Intersect(Worksheets("Enter Here").Columns(2),
Worksheets("Enter Here").UsedRange)

And that works.

Thanks for all your help, Norman. I'm sure I'll need more.

Ken Loomis

 

[Norman Jones]

Hi Ken,

I think I figured out how to do that without activating the sheet.

I changed:

Set rng = Intersect(Columns(2), ActiveSheet.UsedRange)

Actually,

Set rng = Intersect(Columns(2), ActiveSheet.UsedRange)

does not activate the sheet; it merely refers to the sheet which is
currently active.

If you want to ensure that the macro only operates on a specific sheet, by
all means hardcode the sheet name, as you have done. Using ActiveSheet
provides some flexibility in that the macro can be used on any sheet (in any
workbook), providing that the sheet is the active sheet.

 

[Dave Peterson]

or even:

with worksheets("enter here")
Set rng = Intersect(.Columns(2), .UsedRange)
end with

Less typing and easier to read???

 

[Alan Beban]

Is there a way to change all text in a column to upper case.

I tried this:

Worksheets("Enter Here").Range("B:B").Value = _
UCase(Worksheets("Enter Here").Range("B:B").Value)


But, as I am sure most of you here can tell, that did not work.

Is it because I am not accesing the column correctly, or do I just need to
use UCase on each individual cell?

Thanks,
Ken Loomis

Range("A:A").FormulaArray = _
"=UPPER('Enter Here'!" & Worksheets("Enter _
Here").Range("B:B").Address & ")"

Alan Beban