Excel VBA Two functions on one array

Question

I need to delete spaces at the beginning, end of string and make string Proper Case.

I have found two scripts:

Sub Function01()

Dim arrData() As Variant
Dim arrReturnData() As Variant
Dim rng As Excel.Range
Dim lRows As Long
Dim lCols As Long
Dim i As Long, j As Long

  Range("R1", Range("R1").End(xlDown)).Select
  lRows = Selection.Rows.Count
  lCols = Selection.Columns.Count

  ReDim arrData(1 To lRows, 1 To lCols)
  ReDim arrReturnData(1 To lRows, 1 To lCols)

  Set rng = Selection
  arrData = rng.Value

  For j = 1 To lCols
    For i = 1 To lRows
      arrReturnData(i, j) = Trim(arrData(i, j))
      ///ADDING HERE(read below)

    Next i
  Next j

  rng.Value = arrReturnData

  Set rng = Nothing


End Sub

which is deleting spaces on string and another script:

Sub ChangeCase()
    Dim Rng As Range
    On Error Resume Next
    Err.Clear
    Application.EnableEvents = False
    For Each Rng In Selection.SpecialCells(xlCellTypeConstants, _
             xlTextValues).Cells
        If Err.Number = 0 Then
            Rng.Value = StrConv(Rng.Text, vbProperCase)
        End If
    Next Rng
    Application.EnableEvents = True
End Sub

Which is making Proper Case of string. Those two scripts are working on ranges to select all not null cells in R column. I need to make function second script in the first one.

Adding this code in first script at (///ADDING HERE) point: arrReturnData(i, j) = StrConv(arrData(i, j), vbProperCase)

Making my output in Proper Case but with spaces. Could you guys suggest how to make two script functions in a stroke?

Thank you!

Answer 1

This will do the whole without loops:

Sub Function01()    

  Dim rng As Range   

  Set rng = Selection
  rng.Value = rng.Parent.Evaluate("INDEX(PROPER(TRIM(" & rng.Address & ")),)")    

End Sub

Before:

After: