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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

storm8

Active Member
Joined
Apr 7, 2010
Messages
327
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
 

MichPr

New Member
Joined
May 6, 2014
Messages
10
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
 

storm8

Active Member
Joined
Apr 7, 2010
Messages
327
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
 

MichPr

New Member
Joined
May 6, 2014
Messages
10
Thanks again. F8 isn't showing any errors just not executing that part of the code. Will try the change you have suggested. Thanks
 

storm8

Active Member
Joined
Apr 7, 2010
Messages
327
is the range correct? does the messagebox shows the correct range?
add r.select after messagebox to see the range
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,900
Office Version
  1. 365
Platform
  1. Windows
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.
 

storm8

Active Member
Joined
Apr 7, 2010
Messages
327
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?
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,900
Office Version
  1. 365
Platform
  1. Windows
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
 

MichPr

New Member
Joined
May 6, 2014
Messages
10
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.
 

Forum statistics

Threads
1,171,026
Messages
5,873,366
Members
432,976
Latest member
Manbjorngris

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