9

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..

Community
  • 1
  • 1
MartijndR
  • 101
  • 1
  • 6

2 Answers2

11

Try this:

Range("A1:A" & n) = Application.Evaluate("index(lower(A1:A" & n & "),)")
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 4
    This is the correct answer based on the question - square brackets are shorthand notation for `Evaluate` in VBA but only take a string literal, whereas `Evaluate` will accept a concatenated string. – SierraOscar Feb 16 '16 at 18:37
  • 3
    There has been a lot of bantering about the [LOWER](https://goo.gl/GMzwTs) and [UPPER](https://goo.gl/WXHHZ4) functions which are actually paralelled with VBA's [LCase](https://msdn.microsoft.com/en-us/library/office/gg264497.aspx) and [UCase](https://msdn.microsoft.com/en-us/library/office/gg264252.aspx) functions. What hasn't been mentioned is that VBA has no equivalent to the worksheet's native [PROPER](https://goo.gl/9F1oDG) functions and this is an excellent candidate to put that to use. –  Feb 17 '16 at 04:45
6

Looping through the worksheet's cell will slow this down. Grab all of the cell data, process it in memory and then dump the result back to the worksheet.

Sub makeLower()
    Dim v As Long, vLWRs As Variant

    With Worksheets("Sheet1")
        With .Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
            vLWRs = .Value2
            For v = LBound(vLWRs, 1) To UBound(vLWRs, 1)
                vLWRs(v, 1) = LCase(vLWRs(v, 1))
            Next v
            .Cells = vLWRs
        End With
    End With
End Sub

Tested on 50K cell in 0.3 seconds, 1M cells in 6.78 seconds.