2

I'm trying to speed up my For loop as it takes much to long for some sheets with many rows / more columns. Tried already something with array and dictionary, but I don't get it. How can this code be optimized to check the content of cells and insert, if true, new calculated content into other cells? Many thanks!

    Pos = 1
    lngWS = ws_Table.ListRows.Count
    
    For Pos = Pos To lngWS
        If ws_Table.DataBodyRange(Pos, Column_Date).Value2 <> vbNullString And _
           ws_Table.DataBodyRange(Pos, Column_Verify).Value2 <> "OK" Then

            ws_Table.DataBodyRange(Pos, Column_Year).Value2 = Year(ws_Table.DataBodyRange(Pos, Column_Date).Value2)
            ws_Table.DataBodyRange(Pos, Column_Month).Value2 = Format(ws_Table.DataBodyRange(Pos, Column_Date).Value2, "MMMM")

            ws_Table.DataBodyRange(Pos, Column_Compare_1).FormulaLocal = "=1+1"
            ws_Table.DataBodyRange(Pos, Column_Compare_1).Value2 = ws_Table.DataBodyRange(Pos, Column_Compare_1).Value2
            ws_Table.DataBodyRange(Pos, Column_Compare_2).FormulaLocal = "=2+2"
            ws_Table.DataBodyRange(Pos, Column_Compare_2).Value2 = ws_Table.DataBodyRange(Pos, Column_Compare_2).Value2

        End If
    Next Pos
Improve this question

2 Answers 2

Reset to default
2

To speed up working with data in a range, it is much faster to copy the data from the range to an array, work on the array, then copy the data back to the range.

This works for ranges with more than one cell:

Dim Lst as Variant ' The array
Dim Idx as Long ' The row index

' First copy the range to an array
Lst = ws_Table.DataBodyRange
For Idx = LBound(Lst) To UBound(Lst)
    ' Change the rows in the array here, for example:
    '     Lst(Idx, 1) = Lst(Idx, 1) * 3
    '
    ' Be aware that you might need to recreate your formulas, as 
    ' it is the results of the forumalas that are copied to the array:
    '     Lst(Idx, 2) = "=1+1"
Next Idx
' Then copy the array back to the range
ws_Table.DataBodyRange = Lst

The reason working on an array is fast, is because it is an in-memory data structure and a listobject, a range or a cell is a COM object. It takes a long time to reference a COM object.

The method I recommend only reference a COM object 2 or 3 times, whereas the method used in your example or the solution proposed by Tim Williams, references a COM object several times for each row in the range.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

-1

This may be a little faster:

Dim dtVal, verVal, pos As Long
'...
'...

Application.ScreenUpdating = False
For pos = 1 To ws_Table.ListRows.Count
    With ws_Table.DataBodyRange.Rows(pos)
        
        dtVal = .Cells(Column_Date).Value2
        verVal = .Cells(Column_Verify).Value2
        
        If Len(dtVal) > 0 And verVal <> "OK" Then

            .Cells(Column_Year).Value2 = Year(dtVal)
            .Cells(Column_Month).Value2 = Format(dtVal, "MMMM")

            With .Cells(Column_Compare_1)
                .FormulaLocal = "=1+1"
                .Value = .Value
            End With
            With .Cells(Column_Compare_2)
                .FormulaLocal = "=2+2"
                .Value2 = .Value2
            End With
        End If 'have date and not "OK"
    End With
Next pos
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.