Column To Upper() - In place

Sky_B

New Member
Joined
Mar 14, 2016
Messages
6
Is this possible? I'm trying to create a Macro command that will convert a column to upper() without creating a new column and using a =UPPER() formula in the new column.

So far I have this to select the column(s) that are currently active.
Code:
Selection.EntireColumn.Select

I would like to say something like
Code:
Selection.EntireColumn.Select
Selection.UPPER = Selection.EntireColumn.Select

Something along those lines anyway. Is this possible?

Thanks
Sky
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board.

Try something like
Code:
Sub test()
Dim MyArray As Variant, i As Long
With Application.Intersect(Selection.EntireColumn, ActiveSheet.UsedRange)
    MyArray = .Value
    For i = LBound(MyArray) To UBound(MyArray)
        MyArray(i, 1) = UCase(MyArray(i, 1))
    Next i
    .Value = MyArray
End With
End Sub
 

Sky_B

New Member
Joined
Mar 14, 2016
Messages
6
PERFECT!!!!!!

Thank you so much. This will save a ton of time.
Is there an "Answered" or something to say this post has been answered somehow?

thanks again,
Sky
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,686
Office Version
  1. 2010
Platform
  1. Windows
Welcome to the board.

Try something like
Code:
Sub test()
Dim MyArray As Variant, i As Long
With Application.Intersect(Selection.EntireColumn, ActiveSheet.UsedRange)
    MyArray = .Value
    For i = LBound(MyArray) To UBound(MyArray)
        MyArray(i, 1) = UCase(MyArray(i, 1))
    Next i
    .Value = MyArray
End With
End Sub
You can also do it without using a loop (nor restricting the selection to the UsedRange)...
Code:
[table="width: 500"]
[tr]
	[td]Sub MakeUpper()
  Selection = Evaluate(Replace("IF(@="""","""",UPPER(@))", "@", Selection.Address))
End Sub[/td]
[/tr]
[/table]
 
Last edited:

Sky_B

New Member
Joined
Mar 14, 2016
Messages
6

ADVERTISEMENT

Could the same code be used to replace a string within each cell of a column?

Using the Replace to change say "The dog" to "The cat" in every occurrence in a column?
So not changing the content to UPPER but simply replacing without looping?

Thanks,
sky
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,686
Office Version
  1. 2010
Platform
  1. Windows
Could the same code be used to replace a string within each cell of a column?

Using the Replace to change say "The dog" to "The cat" in every occurrence in a column?
So not changing the content to UPPER but simply replacing without looping?
Is "The dog" the only text in the cell? What about letter casing... would "the dog" be replaced by "The cat" as well?
 

Sky_B

New Member
Joined
Mar 14, 2016
Messages
6

ADVERTISEMENT

No, it's not the only text in the field.
My end game is to convert the column to UPPER and then replace (more accurately "remove") specific text from the cell.

Convert: New Member $5 Free Play
To: $5 FREE PLAY

Each cell in the row contains: New Member
The rest of the text in the cell is variable.

I was trying to learn it but with so many ways to do something I was getting lost.

Any thoughts on how to accomplish this?

Thanks,
Sky
 

Sky_B

New Member
Joined
Mar 14, 2016
Messages
6
I feel like I'm close with this but keep getting #VALUE when I run it. Any thoughts?
Code:
 Selection = Evaluate(Replace("IF(@="""","""",UPPER(REPLACE(@,1,11,"")))", "@", Selection.Address))

If i'm thinking correctly, this evaluates
IF @ (cell data) is blank then leave blank
ELSE convert to REPLACE @ (cell data) starting with the 1st character through the 11th character with nothing ("")
THEN convert to UPPER
FINALLY take @ and write it back into Selection.Address (cell)

Is this pretty close?

thanks,
sky
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Why not just remove the text and then use the formula Rick provided?
Code:
Sub test()
With Selection
    .Replace "New Member ", ""
    .Value = Evaluate(Replace("IF(@="""","""",UPPER(@))", "@", Selection.Address))
End With
End Sub
 

Sky_B

New Member
Joined
Mar 14, 2016
Messages
6
Works like a charm. Thank you.
I was using what Rick gave me and trying to adjust it slightly. The formula works when not used as VBA code but instead as an excel formula inside a cell.
Thank you guys so much for being active and nice.

Sky
 

Watch MrExcel Video

Forum statistics

Threads
1,122,672
Messages
5,597,488
Members
414,146
Latest member
marginmakerb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top