Link to home
Create AccountLog in
Avatar of Rayne
RayneFlag for United States of America

asked on

Remove Rows that Dont Matter

Hello All,

I am dealing with a weird situation. There are parts which have dollar number assigned to them for each month.
Now I need to selectively remove only that part (s) that has a sum total of 0 for all the 12 month. How do I do it?
For example in the data – if I auto filter by P3, you see it’s value sums up to 0 for all 12 months – do how do I remove them from the data?
The reason I am asking is that my actual data is 30000 rows and I need something in VBA that does it quickly.  

Thank you
Avatar of Rayne
Flag of United States of America image


if a part's [dollar] column totals for the entire 12 month is non zero number, then Don't remove those 12 rows corresponding to that part.

if a part's [dollar] column totals  for the entire 12 month is 0, then remove all the 12 rows coresponding to that part.
Avatar of redmondb
Hi, Rayne.

Couple of questions, please...
(1) Most important - is the file sorted by Part? If not, is it OK to sort the file?
(2) Does every part have 12 entries?

No need for VBA at all, really.

1) If F3, add a label "Tot", and in F4 the formula =SUMIF(B:B,B4,E:E).  Copy formula down as needed

2) Sort the data using Tot as the first sort field, ascending (so the zeroes get to the top)

3) Use the AutoFilter to select only those rows where Tot = 0

4) You can now quickly delete the filtered rows

5) Turn off AutoFilter

6) Delete Col F if desired

7) Re-sort the data if desired
Avatar of Rayne


Hello All :)

Brian - (1)  is the file sorted by Part? No
 If not, is it OK to sort the file? sure, no problem
(2) Does every part have 12 entries? - YES, each part has 12 months of data

Patrick - that looks great, so just sumif, autofilter and done...
The only thing is I need VBA as the macro will need to execute for all the 20 files it open - so that's why VBA needed

Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Rayne


AWESOME!!! thanks Patrick,

BTW - I like that function name :)

You snooze, you lose.  :)

Option Explicit

Sub Macro12()
Dim xLast_Row As Long
Dim xZero     As Range


xLast_Row = ActiveSheet.UsedRange.Cells(1, 1).Row + ActiveSheet.UsedRange.Rows.Count - 1
If xLast_Row < 4 Then
    MsgBox ("No Data found - run cancelled.")
    Exit Sub
End If

Application.ScreenUpdating = False

    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    Columns("F:F").Insert Shift:=xlToRight
    With Range("F4:F" & xLast_Row)
        .FormulaR1C1 = "=IFERROR(IF(RC[-4]<>R[-1]C[-4],SUM(RC[-1]:R[11]C[-1]),R[-1]C),99999)"
        .Formula = .Value
    End With
    With Range("F3:F" & xLast_Row)
        .AutoFilter Field:=1, Criteria1:="=0", Operator:=xlAnd
    End With
    On Error Resume Next
        Set xZero = Range("F4:F" & xLast_Row).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If xZero Is Nothing Then
        MsgBox ("No Parts are eligible for deletion.")
        MsgBox ("Deleting " & xZero.Rows.Count & " rows...")
    End If
    ActiveSheet.AutoFilterMode = False
    Columns("F:F").Delete Shift:=xlToLeft
Application.ScreenUpdating = True

End Sub

Open in new window

Avatar of Rayne


Hello Brian :)

Thank you. Its always better to have different approaches to a problem.
No problem, Rayne.

A word of caution - while my code doesn't need the Parts to be sorted, it does assume that the 12 months' entries for each Part are together.

Avatar of Rayne


k, I will keep in mind :)