CODEWITHSUNDEEP
vbaexcel
Roi patrick Florentino
Roi patrick Florentino

Reputation: 177

lessen loading or processing Time in VBA

I have this small code which cuts and pastes a cell to another cell if the cell has an empty adjacent cell. But every time I run this code, it usually takes more than a minute to finish the entire column. Here's my small code.

Sub MoveCell()
Dim A As Range

  For Each A In Range("B10:B1000")
    If A <> "" Then
      If A.Offset(0,2) = "" Then
        A.Cut A.Offset(0,4)
      End If
    End If
  Next

End Sub

Is there a way around this code?

Upvotes: 0

Views: 516

Answers (2)

CodeJockey
CodeJockey

Reputation: 1981

Some Suggestions for optimizing:

Do you really need to go all the way to row 1000? Right now, you're processing 990 rows every time you call this code. It is best only go as high as you need to for the application.

Cut and Paste is rather expensive relative to simply setting the cells to a value. Use the following instead:

If A.Offset(0,2) = "" Then  
    A.Offset(0,4) = A

If you STILL need more efficiency, you can load the data into a Variant, process it, and return it to the sheet:

Dim dataVar as Variant
Dim i as Integer

dataVar = Range("B10:F1000")
For i = Lbound(dataVar, 1) to Ubound(dataVar, 1)
' Omitted code for your processing
Next i

Range("B10:F1000") = dataVar

Upvotes: 4

aphoria
aphoria

Reputation: 20189

Setting Application.ScreenUpdating = False before your loop and Application.ScreenUpdating = True after the loop should stop the screen flashing and may improve the time slightly.

Upvotes: 3

Related Questions