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
R
deleteAll12Rows.xlsx
Avatar of Rayne
Rayne
Flag of United States of America image

ASKER

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?

Thanks,
Brian.
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

ASKER

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

R
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

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

ASKER

AWESOME!!! thanks Patrick,

BTW - I like that function name :)
Rayne,

You snooze, you lose.  :)

Option Explicit

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

Sheets("Sheet1").Activate

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
        .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.")
    Else
        MsgBox ("Deleting " & xZero.Rows.Count & " rows...")
        xZero.EntireRow.Delete
    End If
    
    ActiveSheet.AutoFilterMode = False
    Columns("F:F").Delete Shift:=xlToLeft
    
Application.ScreenUpdating = True

End Sub

Open in new window

Regards,
Brian.
Avatar of Rayne

ASKER

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.

Regards,
Brian.
Avatar of Rayne

ASKER

k, I will keep in mind :)