Adding comands to a current Macro

[mlbarry]

Can I just add "Application.Calculation = xlManual" to the beginning and "Application.Calculation = xlAutomatic" to the end of my macro, below. It is taking for ever to run it because it's getting very large. Thank you.


Sub ProcessCash()
lR = Cells(Rows.Count, "A").End(xlUp).Row
For R = lR To 2 Step -1
If Cells(R, 4) = Cells(R - 1, 4) And Cells(R, 11) = Cells(R - 1, 18) Then
Rows(R).Delete
Rows(R - 1).Delete
ElseIf Cells(R, 4) = Cells(R - 1, 4) And Cells(R, 10) + Cells(R, 11) = Cells(R - 1, 18) Then
Rows(R).Delete
Rows(R - 1).Delete
End If
Next R
End Sub

 

[Joe4]

And add "Application.ScreenUpdating = False" at the beginning too and "Application.ScreenUpdating = True" back to the end.

 

[cerfani]

i think when you test equality of range values it will force calculation of underlying formulas so you can give it a try and see if it still deletes the proper rows because you are testing the values of ranges and making decisions based on calculated values.

Perhaps it will save time but I think your sheet will need to calculate each loop regardless.

 

[Fluff]

Another option is to delete all the rows in one hit, which is sometimes quicker

Sub ProcessCash()
   Dim LR As Long
   Dim R As Long
   Dim Rng As Range
   
   With Application
      .ScreenUpdating = False
      .Calculation = xlCalculationManual
   End With
   
   LR = Cells(Rows.Count, "A").End(xlUp).Row
   Set Rng = Range("A" & LR + 1)
   For R = LR To 2 Step -1
      If Cells(R, 4) = Cells(R - 1, 4) And Cells(R, 11) = Cells(R - 1, 18) Then
         Set Rng = Union(Rng, Range("A" & R - 1).Resize(2))
      ElseIf Cells(R, 4) = Cells(R - 1, 4) And Cells(R, 10) + Cells(R, 11) = Cells(R - 1, 18) Then
         Set Rng = Union(Rng, Range("A" & R - 1).Resize(2))
      End If
   Next R
   Rng.EntireRow.Delete
   
   With Application
      .ScreenUpdating = True
      .Calculation = xlCalculationAutomatic
   End With

End Sub

 

[mlbarry]

I'm getting an error when running the Macro.

It's highlighting this row:

Rng.EntireRow.Delete

 

[Fluff]

What is the error message?
Do you have any sheet protection?

 

[mlbarry]

I'm sorry I was in the middle of something else so I just quickly went through the error message. I want to say I saw an error 404, but not sure. I have no protections on. I'm not going to reset to run it today to find out the error message was, I'll have to wait until Monday. Thank you I appreciate your help. If you have something I'll try it, if not, I'll see what happens Monday.

 

[mlbarry]

The error is:
Run-Time error '1004':
Delete method of Range class failed

Points to: "Rng.EntireRow.Delete" in the macro.

 

[Joe4]

Do you have any protected of merged cells on your sheet?

If you temporarily change this line:

[COLOR=#333333]Rng.EntireRow.Delete

to this:
[/COLOR]

[COLOR=#333333]MsgBox Rng.Address
[/COLOR]

It may clarify what the issue is when you see what range it is trying to delete.
Make sure it is not empty (nothing).

 

[Fluff]

Another option to test is change that line to

   Rng.EntireRow.Interior.Color = 1234

Which should highlight what rows the macro is trying to delete