CODEWITHSUNDEEP
performanceexcellagvba
rei123
rei123

Reputation: 63

VBA code causing excel lagging

My code below is used to decrements date based on the first date entered, and if column B is filled. As the data imported to Excel is very large, so this calculation is now causing my excel to lag. Is there a way to speed it up??

For i = 1 To rowNow - 3

newDate = DateAdd("d", -i, oldDate)

For Each Cell In Range("A:A").Cells

If IsEmpty(Cell) = True And IsEmpty(Range("B:B")) = False Then Cell.Value = newDate: Exit For

Next
Next

Upvotes: 1

Views: 621

Answers (1)

SJR
SJR

Reputation: 23081

Maybe this,depending on what's in column B:

For i = 1 To rowNow - 3
    newDate = DateAdd("d", -i, oldDate)
    With Range("A:A")
        On Error Resume Next
        Intersect(.SpecialCells(xlCellTypeBlanks), .Offset(, 1).SpecialCells(xlCellTypeConstants).Offset(, -1))(1).Value = newDate
    End With
Next i

Upvotes: 2

Related Questions