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

 

[mlbarry]

This (Rng.EntireRow.Interior.Color = 1234) did highlight the rows that should have been deleted. I'm a macro novic so I don't understand what this accomplished. Is there a way to remove them automatically?

 

[Fluff]

That test was just to check that it was correctly finding the correct rows to delete.
Do you have any merged cells?

 

[Joe4]

Do you have any merged cells?

...or protected cells, like I asked back in post 9?

 

[mlbarry]

I did answer if I had any thing protected in post #6 , no to protected and to merged.

I did notice that the very last row was highlighted, it was not in the table, other than that it worked but didn't delete the rows nor did it turn back on the calculating function.

 

[Fluff]

Is your data in a proper table?

 

[mlbarry]

Yes it is in a Table.

 

[Fluff]

That's why it doesn't work. Try

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
   For R = LR To 2 Step -1
      If Cells(R, 4) = Cells(R - 1, 4) And Cells(R, 11) = Cells(R - 1, 18) Then
         If Rng Is Nothing Then
            Set Rng = Range("A" & R - 1).Resize(2)
         Else
            Set Rng = Union(Rng, Range("A" & R - 1).Resize(2))
         End If
      ElseIf Cells(R, 4) = Cells(R - 1, 4) And Cells(R, 10) + Cells(R, 11) = Cells(R - 1, 18) Then
         If Rng Is Nothing Then
            Set Rng = Range("A" & R - 1).Resize(2)
         Else
            Set Rng = Union(Rng, Range("A" & R - 1).Resize(2))
         End If
      End If
   Next R
   Rng.EntireRow.Delete
   
   With Application
      .ScreenUpdating = True
      .Calculation = xlCalculationAutomatic
   End With

End Sub

 

[mlbarry]

This one gave me the same error message: Run-Time error '1004': - Delete method of Range class failed - Points to: "Rng.EntireRow.Delete" in the macro.

I just combined mine and yours, see below. This did what I wanted, it turned off calculating when started and back on at the end. Thank you all for your help.

If you have a fix I'll try it, if you don't then I'm okay with what I have.

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
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

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Sub