CODEWITHSUNDEEP
excelvbavectorization
Nicholas Kemp
Nicholas Kemp

Reputation: 337

Apply calculations to whole range instead of looping though each cell VBA

So I have to loop through a range of about 10000 cells containing strings and shortening them to 255 characters. My actual code is like this:

For i = firstRow to lastRow
    Range("X" & i) = Left(Range("X" & i),255)
Next i

However, this is extremely slow, so I was wondering if there was anyway to do that once on the whole range instead of looping through each cell of the range, or if there was any other way that is more efficient than doing it my way.

Upvotes: 0

Views: 415

Answers (3)

Tim Williams
Tim Williams

Reputation: 166735

Or this:

With ActiveSheet.Range("X" & firstRow & ":X" & lastRow)
    .Value = .Parent.Evaluate("LEFT(" & rng.Address & ",255)")
End With

Upvotes: 1

Error 1004
Error 1004

Reputation: 8230

You try:

Option Explicit

Sub test()

    Dim Lastrow As Long, FirstRow, i As Long

    With ThisWorkbook.Worksheets("Sheet1")

        Lastrow = .Cells(.Rows.Count, "X").End(xlUp).Row
        FirstRow = 1

        For i = FirstRow To Lastrow
            .Range("X" & i).Value = Left(.Range("X" & i).Value, 255)
        Next i

    End With

End Sub

Or

Option Explicit

Sub test()

    Dim Lastrow As Long, FirstRow
    Dim rng As Range, cell As Range

    With ThisWorkbook.Worksheets("Sheet1")

        Lastrow = .Cells(.Rows.Count, "X").End(xlUp).Row
        FirstRow = 1
        Set rng = .Range(.Cells(FirstRow, 24), Cells(Lastrow, 24))

        For Each cell In rng
            cell.Value = Left(cell.Value, 255)
        Next cell

    End With

End Sub

Upvotes: 1

Damian
Damian

Reputation: 5174

Try this:

Sub short()

    Dim arr, FirstRow As Long, LastRow As Long, i As Long

    arr = ThisWorkbook.Sheets("yoursheetname").Range("X" & FirstRow, "X" & LastRow).Value

    For i = 1 To UBound(arr)
        arr(i, 1) = Left(arr(i, 1), 255)
    Next i

    ThisWorkbook.Sheets("yoursheetname").Range("X" & FirstRow, "X" & LastRow).Value = arr



End Sub

When you work with thousands of rows or cells, arrays are the best way to go.

Upvotes: 3

Related Questions