Help with VBA for propercase

[boolean]

I have an excel sheet where I want all cells to have propercase. (First letter of each word is capital) I've seen several macros that do this but they all give me errors. Also, is it possible to save the macro to the personal.xlsb file so I could run it on any sheet?

[MarvinP]

Hi boolean and welcome to the forum.

There isn't a Proper function living in VBA so you need to use the Excel function like this.

If A1 = Bob and mary went to the store.

Using Excel Proper (A1) gives

Bob And Mary Went To The Store.

You can try to write one like at http://www.mvps.org/dmcritchie/excel/proper.htm

But it is a lot easier to simply call the worksheet function from Excel into your VBA code like

WorksheetFunction.Proper(Range("A1")) and it will return what you want.

I hope this helps.

[shg]

Please Login or Register  to view this content.

[MarvinP]

I stand corrected! shg is the master!!

There is a proper case in VBA.

see all of them at http://msdn.microsoft.com/en-us/libr...73(VS.60).aspx

[boolean]

Here we go!

Please Login or Register  to view this content.

[boolean]

Please Login or Register  to view this content.

This macro works when I set the range to A1:A90000 but it freezes up when I select the entire column A:A. I found a couple workarounds that use a large amount of VBA just to return the last cell in a column. is there a better way to select all used cells in specific columns?

[MarvinP]

Hi,
Use code like:

Please Login or Register  to view this content.

Then define your range as

Please Login or Register  to view this content.

see if that works for youj.

[shg]

One way:

Please Login or Register  to view this content.

[boolean]

Hi,
Use code like:

Please Login or Register  to view this content.

Then define your range as

Please Login or Register  to view this content.

see if that works for youj.

Thanks a ton, that works for a single column.

How could I select multiple columns with this. I think I could reuse the & LastRow because column A will always have as many entries as all the other cells.

Please Login or Register  to view this content.

I tried this but it gave a syntax error.

[MarvinP]

Look at shg's UsedRange above.
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
or search the net for VBA UsedRange for more examples.