PDA

View Full Version : Solved: If Subtotal equal to zero delete that paticular group.



Shazam
01-14-2008, 12:32 PM
Hi Everyone,


I have a workbook that uses Subtotals. My question is does anyone has a code that will look in column D and if the subtotal equal to zero then delete that subtotal group.

Here is a sample of my workbook.

Bob Phillips
01-14-2008, 02:00 PM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim rng As Range
Dim StartRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
StartRow = 2
For i = 2 To LastRow

If .Cells(i, "A").Value = "Total" Then

If .Cells(i, "D").Value = 0 Then

If rng Is Nothing Then

Set rng = .Rows(StartRow).Resize(i - StartRow + 1)
Else

Set rng = Union(rng, .Rows(StartRow).Resize(i - StartRow + 1))
End If
End If

StartRow = i + 1
End If
Next i

If Not rng Is Nothing Then rng.Delete
End With

End Sub

Shazam
01-14-2008, 03:24 PM
awesome!!! Thank you xld.