[A1:A20] How can I use variables instead of "A1" or "A20" or "A" or "20" etc

Hi,

Sub Sample()
MyVal = Application.WorksheetFunction.Count(A:A)
[A1:A20] = [INDEX(Lower(A1:A20),)]
End Sub

How can I replace A20 with MyVal please.

This is a wonderful one liner which enables me to convert text to UPPER or LOWER case without the need for a loop & UCASE/LCASE. But how can I use variables with [ ]...

Thank you

Quote from rory

You can't use variables with [] notation. Use Evaluate instead:

Code

Range("A1:" & lastCell).value = Evaluate("INDEX(Lower(A1:" & lastCell & "),)")

Thanks Rory, that worked perfectly.

Glad you didn't reply in Groot language otherwise, I would have been "stumped"

All the best to you