Convert entire range to lowercase without looping through cells Indirect

Question

I'm looking at VBA code that takes an entire range of cells and converts them into lowercase. I found the following:

[A1:A20] = [index(lower(A1:A20),)]

This works fine for a fixed range (don't entirely understand syntax, but found the following post:)

Post detailing code above

My problem is this:

I would like to be able to set the range dynamically as I'm dealing with changing range sizes. However, the following doesn't work, and I can't seem to be able to use INDIRECT() either in VBA.

Range("A1:A" & n) = [index(lower(Range("A1:A" & n)),)]

Is there a way to make this work? I would really like to avoid using a for loop as I suspect this should be a lot faster..

Answer 1

Try this:

Range("A1:A" & n) = Application.Evaluate("index(lower(A1:A" & n & "),)")