How to convert several non-adjacent columns to lowercase-CodePudding

This is looping through a worksheet that is about 10k rows and it is taking a considerable amount of time. Is there a way to do this faster aside from an array? thank you

For i = 2 To spberowcnt
    With spbe30
    .Range("b" & i).Value = LCase(.Range("b" & i).Value)
    .Range("d" & i).Value = LCase(.Range("d" & i).Value)
    .Range("i" & i).Value = LCase(.Range("i" & i).Value)
    .Range("j" & i).Value = LCase(.Range("j" & i).Value)
    .Range("l" & i).Value = LCase(.Range("l" & i).Value)
    .Range("m" & i).Value = LCase(.Range("m" & i).Value)
    .Range("n" & i).Value = LCase(.Range("n" & i).Value)
    .Range("p" & i).Value = LCase(.Range("p" & i).Value)
    .Range("q" & i).Value = LCase(.Range("q" & i).Value)
    .Range("r" & i).Value = LCase(.Range("r" & i).Value)
    .Range("z" & i).Value = LCase(.Range("z" & i).Value)
    .Range("aa" & i).Value = LCase(.Range("aa" & i).Value)
    End With
Next i

For i = 2 To spberowcnt
    With spbe60
    .Range("b" & i).Value = LCase(.Range("b" & i).Value)
    .Range("d" & i).Value = LCase(.Range("d" & i).Value)
    .Range("i" & i).Value = LCase(.Range("i" & i).Value)
    .Range("j" & i).Value = LCase(.Range("j" & i).Value)
    .Range("l" & i).Value = LCase(.Range("l" & i).Value)
    .Range("m" & i).Value = LCase(.Range("m" & i).Value)
    .Range("n" & i).Value = LCase(.Range("n" & i).Value)
    .Range("p" & i).Value = LCase(.Range("p" & i).Value)
    .Range("q" & i).Value = LCase(.Range("q" & i).Value)
    .Range("r" & i).Value = LCase(.Range("r" & i).Value)
    .Range("z" & i).Value = LCase(.Range("z" & i).Value)
    .Range("aa" & i).Value = LCase(.Range("aa" & i).Value)
    End With
Next i

CodePudding user response：

This is the array solution

Sub test()


Application.ScreenUpdating = False


Dim arrWorksheets(1) As Variant, ws As Worksheet
Set arrWorksheets(0) = spbe30
Set arrWorksheets(1) = spbe60

Dim arrColumns As Variant
arrColumns = Array("B", "D", "AA")  'adjust to your needs

Dim arrValues As Variant

Dim iWs As Long, iC As Long, i As Long

For iWs = 0 To UBound(arrWorksheets)
    Set ws = arrWorksheets(iWs)
    
    For iC = 0 To UBound(arrColumns)
        arrValues = ws.Range(arrColumns(iC) & "2").Resize(spberowcnt).Value
    
        For i = 1 To UBound(arrValues, 1)
            arrValues(i, 1) = LCase(arrValues(i, 1))
        Next
        
        ws.Range(arrColumns(iC) & "2").Resize(spberowcnt).Value = arrValues
    Next
Next
    
Application.ScreenUpdating = True

End Sub

Alternative: helper columns ...

CodePudding user response：

You could try something like the following, looping over the columns instead of the individual cells and using Evaluate and Lower on the entire column. You could also process adjacent columns together.

cols = Array("B", "D", "I:J", "L:N", "P:R", "Z:AA")

For i = LBound(cols) to Ubound(cols)
    Dim col As String
    col = cols(i)

    With spbe30
        Dim rng As Range
        Set rng = .Rows("2:" & spberowcnt).Columns(col)

        rng.Value = .Evaluate("LOWER(" & rng.Address & ")")
    End With
Next

But as mentioned in comments, an array is probably the way to go.