Delete rows with Macro

[Horby]

Hi, How do I delete a set of rows automatically. See the picture

 

[Rick Rothstein]

Yes, All cells. 1 to last cell. A:A

Filling all those cells with zeroes seem wasteful and inefficient; however, if that is what you have, then give this code a try...

Sub DeleteRows()
  If Application.CountIf(Columns("A"), 0) = Rows.Count Then Range("A6:D" & Rows.Count).ClearContents
End Sub

 

[Horby]

Filling all those cells with zeroes seem wasteful and inefficient; however, if that is what you have, then give this code a try...

Sub DeleteRows()
  If Application.CountIf(Columns("A"), 0) = Rows.Count Then Range("A6:D" & Rows.Count).ClearContents
End Sub

Unfortunately does not work! i need is if total column A = 0
If this condition is met is executed code.

 

[Rick Rothstein]

Unfortunately does not work! i need is if total column A = 0
If this condition is met is executed code.

But that is what my code does.. if every cell in Column A equals a zero (from A1 down to A1038576), then A6:D1038576 is cleared, otherwise nothing happens. If that is not working for you, then there is something different about your cells that you are not telling us. Another possibility, I guess, is if you have a Change event procedure that fills in blank cells... that would kick in after my macro runs and "undo" things.

 

[Horby]

But that is what my code does.. if every cell in Column A equals a zero (from A1 down to A1038576), then A6:D1038576 is cleared, otherwise nothing happens. If that is not working for you, then there is something different about your cells that you are not telling us. Another possibility, I guess, is if you have a Change event procedure that fills in blank cells... that would kick in after my macro runs and "undo" things.

I want total cells, not every cell. if SUM(A:A)=0


See the picture.

 

[Rick Rothstein]

Sorry, I misunderstood your response to my question. Give this code a try then...

Sub DeleteRows()
  If Application.Sum(Columns("A")) = 0 Then Range("A6:D" & Rows.Count).ClearContents
End Sub

 

[Horby]

Sorry, I misunderstood your response to my question. Give this code a try then...

Sub DeleteRows()
  If Application.Sum(Columns("A")) = 0 Then Range("A6:D" & Rows.Count).ClearContents
End Sub

Excellent, works successfully!

Finally
Can I show an alert message if the code is not executed?

 

[Horby]

After this Modified:

Sub DeleteRows()
  If Application.Sum(Columns("A")) = 0 Then
  Range("A6:D" & Rows.Count).ClearContents
  Else
 MsgBox ("No deletion!")
  End If
End Sub

Works successfully!

Thank you for your help, and thanks to all.