How to delete rows automatically with values zero out?

  • Thread starter Thread starter Tracey
  • Start date Start date
T

Tracey

Hello,

I have a spreadsheet with hundreds of rows. Many of them were entered
at the beginning and then were reversed out. I got this spreadsheet
from our ERP system. It is just like this:
Part# Quantity Value
013-001 1 $1.00
013-001 -1 $1.00-
013-002 5 $25.00
013-007 3 $120.00
013-007 -3 $120.00-
022-001 12 $17.00
041-009 7 $251.00
041-009 -7 $251.00-
052-061 10 $30.00

How can I delete those rows autometically with the quantity were zero
out. I don't want them show up on my spreadsheet. Do I have to use
VBA? If so, could you please help me on the programme?

Thanks,
Tracey
 
This assumes that there are NO duplicates that are NOT "zeroed out"

Sub delnulifiedrows()
mc = 1 ' col A
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If Cells(i - 1, mc) = Cells(i, mc) Then Rows(i - 1).Resize(2).Delete
Next i
End Sub

Part# Quantity Value
013-002 5 25
022-001 12 17
052-061 10 30
 
This assumes that there are NO duplicates that are NOT "zeroed out"

Sub delnulifiedrows()
mc = 1 ' col A
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If Cells(i - 1, mc) = Cells(i, mc) Then Rows(i - 1).Resize(2).Delete
Next i
End Sub

      Part#   Quantity Value
      013-002    5 25
      022-001    12 17
      052-061     10 30
--
Don Guillett
Microsoft MVP Excel
SalesAid Software








- Show quoted text -
Click to expand...

Hi Don,

I run this vba with my spreadsheet, it works. But there are some
issues like this:
052-010 1467 $20
052-010 356 $5
052-010 -1467 $20-
052-010 -356 $5-
052-010 1467 $20
052-010 -1639 $22
052-010 -329 $4.5-
After run the vba, 052-010 does not show up on my spreadsheet at all.
The last three items should be there, because they are not zeroed out.

And this:
053-095 18.9 $2
053-095 -352 $37-
053-095 -320 $33-
053-095 352 $37
053-095 -352 $37-
After run the vba, only 053-095 18.9 $2 show up on the
spreadsheet, which is supposed to have more.

Could you please help me to fix the problem?

Thank you very much,
Tracey
 
Please TOP post when replying to ME.
Your FIRST example showed ONLY rows that zeroed out. It did not show ANY
that had the same number and a different col B.

Sub delnulifiedrows()
lr = Cells.find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious).Row
'MsgBox lr
mc = 1 ' col A
On Error Resume Next
For i = lr To 3 Step -1
If Cells(i - 1, mc) = Cells(i, mc) And _
Abs(Cells(i - 1, mc + 1)) = Abs(Cells(i, mc + 1)) Then
Rows(i - 1).Resize(2).Delete
End If
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
This assumes that there are NO duplicates that are NOT "zeroed out"

Sub delnulifiedrows()
mc = 1 ' col A
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If Cells(i - 1, mc) = Cells(i, mc) Then Rows(i - 1).Resize(2).Delete
Next i
End Sub

Part# Quantity Value
013-002 5 25
022-001 12 17
052-061 10 30
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
message








- Show quoted text -
Click to expand...

Hi Don,

I run this vba with my spreadsheet, it works. But there are some
issues like this:
052-010 1467 $20
052-010 356 $5
052-010 -1467 $20-
052-010 -356 $5-
052-010 1467 $20
052-010 -1639 $22
052-010 -329 $4.5-
After run the vba, 052-010 does not show up on my spreadsheet at all.
The last three items should be there, because they are not zeroed out.

And this:
053-095 18.9 $2
053-095 -352 $37-
053-095 -320 $33-
053-095 352 $37
053-095 -352 $37-
After run the vba, only 053-095 18.9 $2 show up on the
spreadsheet, which is supposed to have more.

Could you please help me to fix the problem?

Thank you very much,
Tracey
 
Back
Top