Dynamic range to convert column into upper case-CodePudding

Sub Trim(sh,FirstCell,LastCell)
    Sheets(sh).select
    Range(FirstCell,LastCell) = [index(Upper(FirstCell,LastCell),)]
End Sub

I want to do something like this to make it dynamic. Calling this macro through other application where I'm giving input for sheet name first cell and last cell of the range.

CodePudding user response：

One cannot use [] with variables it expects a string. Also ranges already have the Sheet in the variable and adding it is not needed:

Sub MyTrim(rng as Range)    
    rng.value = rng.Parent.Evaluate("index(Upper(" & rng.adress & "),)")
End Sub

Or use UCase:

Sub MyTrim(rng as Range)
    If rng.Cells.Count = 1 Then
        rng = UCase(rng)
        Exit Sub
    End If
    Dim RngArray() as Variant
    RngArray = rng

    Dim i as Long
    For i = 1 to Ubound(RngArray,1)
        Dim j as Long
        For j = 1 to Ubound(RngArray, 2)
            RngArray(i,j) = UCase(RngArray(i,j))
        Next j
    Next i
    rng = rngarray
End Sub

CodePudding user response：

Upper-Case a Column Using `Evaluate`

Sub UCaseColumnTEST()
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    UCaseColumn ws, "A1", "A20"
End Sub

Sub UCaseColumn( _
        ByVal ws As Worksheet, _
        ByVal FirstCellAddress As String, _
        ByVal LastCellAddress As String)
    With ws.Range(FirstCellAddress, LastCellAddress)
        .Value = ws.Evaluate("UPPER(" & .Address & ")")
    End With
End Sub

Upper-Case a Column Using Evaluate

Upper-Case a Column Using `Evaluate`