Convert an entire range to uppercase without looping through all the cells

Question

right now I'm using the following code to convert a list of ticker symbols from lowercase to upper case letters:

Dim Tickers As String
Dim n As Integer
For n = 2 To Last
    Tickers = UCase(W.Cells(n, 1).Value)
    W.Cells(n, 1).Value = Tickers
Next n

Is there a method I can use to convert the whole range in one line? something like:

Range("A1:A20").convertouppercasesomehow

Answer 1

Is there a method I can use to convert the whole range in one line?

Yes you can convert without looping. Try this

Sub Sample()
    [A1:A20] = [INDEX(UPPER(A1:A20),)]
End Sub

Alternatively, using a variable range, try this:

Sub Sample()
    dim rng as Range
    dim sAddr as string

    set rng = Range("A1:A20")
    sAddr = rng.Address

    rng = Evaluate("index(upper(" & sAddr & "),)")

End Sub

As per your example

W.Range("A1:A20") = [index(upper(A1:A20),)]

Explanation

There are two parts to [A1:A20] = [INDEX(UPPER(A1:A20),)]

PART 1

As shown above, [A1:A20] is nothing but just a short way of writing Range("A1:A20")

PART 2

[INDEX(UPPER(A1:A20),)]

Index and Upper are worksheet functions. So you can use Application.Worksheetfunction.Index() but since we don't have an equivalent of UPPER like Application.Worksheetfunction.UPPER(), we can only write it as [cell] = [UPPER(cell)]

Now with that line we are instructing VBA to return an array and this is where INDEX comes into play. (As we are aware, there are two forms of the INDEX function: the array form and the reference form.) By not specifying a row or a column of the array, we are simply letting Excel know that we want the whole array. (Mentioned in VBA help as well) So basically what we are doing is converting each cell in [A1:A20] into uppercase