macro - select unknown data range and convert to proper case

MichPr

New Member
Joined
May 6, 2014
Messages
10
Hi,

I have a different excel spreadsheets which I must convert to PROPER case

My difficulty is that have two macros that seem work individually and I am wondering how I can combine both and if this is possible.

Firstly I am using the following to select the 'range' of values

Sub SelectDataRange()
Dim LastRow As Long, LastColumn As Long
LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).<wbr>Row
LastColumn = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).<wbr>Column
Range("A1").Resize(LastRow, LastColumn).Select
MsgBox "The data range address is " & Selection.Address(0, 0) & ".", 64, "Data-containing range address:"
End Sub


and then secondly I hope to use something like this to change the selected range to proper case but I new to this and really struggling with the following and also with combining both

Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell = Application.Proper(cell)
End If
Next

I have also tried the following but only experimented by using a defined range so it isn't of much use to me.

Sub Proper_Case()
For Each x In Range("C1:C5")

x.Value = Application.Proper(x.Value)
Next
End Sub


Any help would be greatly appreciated.

Thank you
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
in first macro change this line

Code:
[COLOR=#333333]Range("A1").Resize(LastRow, LastColumn).Select
[/COLOR]

to

Code:
[COLOR=#333333]
dim r as range, c as range
set r = [/COLOR][COLOR=#333333]Range("A1").Resize(LastRow, LastColumn)
for each c in r.cells
[/COLOR][COLOR=#333333]If c.HasFormula = False Then[/COLOR]
[COLOR=#333333]c.value = Application.Proper(c.value)[/COLOR]
[COLOR=#333333]End If[/COLOR][COLOR=#333333]
next c
[/COLOR]

and on next line Selection.address must be r.address
 
Upvote 0
Hi, many thanks

I now have something like this: The code below is finding the range successfully but does not convert the text to proper case. Hoping you may be able to advise. It's all very new to me so apologies if I have made a very basic error.


Sub Propercase()

Dim LastRow As Long, LastColumn As Long
LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastColumn = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Dim r As Range, c As Range
Set r = Range("A1").Resize(LastRow, LastColumn)
MsgBox "The data range address is " & r.Address(0, 0) & ".", 64, "Data-containing range address:"
For Each c In r.Cells
If c.HasFormula = False Then
c.Value = Application.Proper(c.Value)
End If
Next c
End Sub
 
Upvote 0
maybe
c.Value = worksheetfunction.Proper(c.Value)

can yous tep trough the code (with F8) and see where the issue might be? the code looks otherwise fine to me
 
Upvote 0
Thanks again. F8 isn't showing any errors just not executing that part of the code. Will try the change you have suggested. Thanks
 
Upvote 0
is the range correct? does the messagebox shows the correct range?
add r.select after messagebox to see the range
 
Upvote 0
Try this:
Rich (BB code):
Sub Propercase()

Dim LastRow As Long, LastColumn As Long
Dim r As Range, c As Range

LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastColumn = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Set r = Range("A1").Resize(LastRow, LastColumn)

MsgBox "The data range address is " & r.Address(0, 0) & ".", 64, "Data-containing range address:"

r.SpecialCells(xlCellTypeConstants).Select

For Each c In r.SpecialCells(xlCellTypeConstants)
    c.Value = StrConv(c.Value, vbProperCase)
Next c

Set r = Nothing: Set c = Nothing

End Sub
The line in red should only select cells that do not contain a formula.
 
Upvote 0
Jack, why would you set r and c to nothing when they go out of scope after the sub finishes and GC kicks in? Just a habit or am I wrong?
 
Upvote 0
Habit more than anything else; you are right about going out of scope when the sub finishes, though did find this reason:
VBA uses a garbage collector which is implemented by reference counting.
There can be multiple references to a given object (for example, <code style="margin: 0px; padding: 1px 5px; border: 0px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: pre-wrap;">Dim aw = ActiveWorkbook</code> creates a new reference to Active Workbook), so the garbage collector only cleans up an object when it is clear that there are no other references. Setting to Nothing is an explicit way of decrementing the reference count. The count is implicitly decremented when you exit scope.
Strictly speaking, in modern Excel versions (2010+) setting to Nothing isn't necessary, but there were issues with older versions of Excel (for which the workaround was to explicitly set)

Via this link: When should an Excel VBA variable be killed or set to Nothing? - Stack Overflow
 
Upvote 0
Hi, tried what you both suggested. Range is selected without any problems but the case is not changed.
When I F8 through the code I sometimes get an error displayed on the following lines


LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastColumn = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column


Thanks again for you help.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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
Back
Top