VBA Excel - changing case to Proper Case

Question

I'm new to Stackoverflow so hopefully I have posted this question in the right place.

I'm having trouble getting my code to work in VBA. I want it to select columns D:F until the last cell value. With this selection, I would like to change the case of the cells (they are currently uppercase) to Proper case.

Dim Lastrow As Integer
Dim range As Variant

With Worksheets("Overdue PO")
    Lastrow = .Cells(Rows.Count, "D").End(xlUp).Row
    .range("D2:F" & Lastrow).Select
    range = Selection.Value
End With

Application.Proper (range)

It currently selects the range until the bottom row, but it doesn't change the case of text. No error appears when running the code.

Thanks in advance :)

http://stackoverflow.com/questions/19985895/convert-an-entire-range-to-uppercase-without-looping-through-all-the-cells Similarly you can use `[A1:A20] = [INDEX(PROPER(A1:A20),)]` — Siddharth Rout, Apr 13 '16 at 14:52

Scott Craner · Answer 1 · 2016-04-13T14:51:01.940

4

Try this:

Dim Lastrow As Integer

With Worksheets("Overdue PO")
    Lastrow = .Cells(Rows.Count, "D").End(xlUp).Row
    .Range("D2:F" & Lastrow).Value = .Evaluate("INDEX(PROPER(D2:F" & Lastrow & "),)")
End With

It will be near instant, without the need for loops.

Also using .Select or .Activate also slow down the code, aoid them if possible by referring to the cells directly.

edited Apr 13 '16 at 14:51

answered Apr 13 '16 at 14:41

Scott Craner

148,073
10
49
81

@Amy in about 7-8 minutes you will be able to choose one to mark as correct. You do this by clicking the grey/green check mark by the answer. Please come back and mark one as correct, it is something only you can do and it will close out the question as being answered. – Scott Craner Apr 13 '16 at 14:47
@Amy yes you have. – Scott Craner Apr 13 '16 at 14:53
@SiddharthRout just standing on the shoulders of giants like yourself. – Scott Craner Apr 13 '16 at 14:57

score 1 · Accepted Answer · answered Apr 13 '16 at 14:48

1

Try this

Sub test()

Dim Lastrow As Integer
Dim range As range
Dim c As range


With Worksheets("Overdue PO")
Lastrow = Columns("D:F").Cells.Find(What:="*", LookIn:=xlValues,   SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Row
    .range("D2:F" & Lastrow).Select
    Set range = Selection
End With

For Each c In range
    c.Value = Application.WorksheetFunction.Proper(c.Value)
Next c


End Sub

answered Apr 13 '16 at 14:48

jcarroll

577
2
15

This works well too :) thanks for your help! @jcarroll – Amy Apr 13 '16 at 15:11
I would probably recommend combing the LastRow = ... line from my code with Scott's answer. – jcarroll Apr 13 '16 at 15:13

score 0 · Answer 3 · answered Apr 13 '16 at 14:40

0

Application.WorksheetFunction.Proper(range) should do it. See https://msdn.microsoft.com/en-us/library/office/ff834434.aspx for documentation on WorksheetFunction

answered Apr 13 '16 at 14:40

Munir

3,442
3
19
29

score 0 · Answer 4 · edited Aug 03 '18 at 01:27

I wanted to use this with a named range, and was kindly provided the following code. Perhaps it will assist someone else:

Sub m_MakeProper()
'2/8/2018
'
Application.Volatile True
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.DisplayStatusBar = False
'
Worksheets("test").Activate
'
Dim LastRow                       As Long
Dim LastCol                       As Integer
Dim cell                          As Variant
Dim thiswksht                     As Worksheet
Dim thiswb                        As Workbook
'
'
Set thiswksht = ActiveSheet
If thiswksht.AutoFilterMode Then
    AutoFilterMode = False
End If
'
With thiswksht
    LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
End With
'
Cells(1, 1).Activate
Rows(1).find("Status").Select
Range(ActiveCell.Address, Cells(LastRow, ActiveCell.Column)).Select
Selection.Name = "c_P_Status"
With Range("c_P_Status")
    .Value = Application.Evaluate("INDEX(PROPER(" & .Address & "),0)")
End With
'
End Sub

Ops, obviously missed getting it all in the correct place. Thanks — Ron, Aug 06 '18 at 20:13

VBA Excel - changing case to Proper Case

4 Answers4

Linked