How to delete rows automatically with values zero out?

[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

 

[Don Guillett]

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

 

[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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software








- Show quoted text -

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

 

[Don Guillett]

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 -

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