Convert Case Without Loop

[Nanogirl21]

Is there a code that I can add in my VBA that will convert selected columns to proper case without looping?

columns = m, q, ae, and af.

 

[footoo]

Dim rng As Range
Set rng = Intersect([M:M], ActiveSheet.UsedRange)
rng = Evaluate("index(proper(" & rng.Address & "),)")

Note : rng needs to be one area only (i.e. contiguous cells)

 

[mse330]

The below code is using a loop but it should be pretty quick as I copy to the data to an array then copy back into the sheet …

Sub ProperCase()

Dim Arr() As Variant, Rg As Range, lRow As Long

For i = 13 To 32
    If i = 13 Or i = 17 Or i = 31 Or i = 32 Then
        lRow = ActiveSheet.Cells(Rows.Count, i).End(xlUp).Row
        Set Rg = ActiveSheet.Range(Cells(1, i), Cells(lRow, i))
            Arr = Rg
                For x = LBound(Arr) To UBound(Arr)
                    Arr(x, 1) = WorksheetFunction.Proper(Arr(x, 1))
                Next x
            Rg = Arr
    End If
Next i
    
End Sub

 

[Nanogirl21]

Dim rng As Range
Set rng = Intersect([M:M], ActiveSheet.UsedRange)
rng = Evaluate("index(proper(" & rng.Address & "),)")

<g class="gr_ gr_9 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="9" data-gr-id="9">Note :</g> rng needs to be one area only (i.e. contiguous cells)

This worked perfectly and was easy to understand. How would I modify to use other formats (ie: all lower case, all upper case, sentence case). Thank you.

 

[footoo]

How would I modify to use other formats (ie: all lower case, all upper case, sentence case).

rng = Evaluate("index(lower(" & rng.Address & "),)")


rng = Evaluate("index(upper(" & rng.Address & "),)")

 

[footoo]

To capitalise first letter only :

Dim rng$: rng = Intersect([M:M], ActiveSheet.UsedRange).Address
Range(rng) = Evaluate("INDEX(UPPER(LEFT(" & rng & ",1))&LOWER(RIGHT(" & rng & ",LEN(" & rng & ")-1)),)")