How to delete rows automatically with values zero out?

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
0
Tracey
11/19/2009 9:33:41 PM
excel 39879 articles. 2 followers. Follow

3 Replies
2387 Views

Similar Articles

[PageSpeed] 11

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
dguillett1@austin.rr.com
"Tracey" <xinxindong2004@gmail.com> wrote in message 
news:07d81668-cee7-41a8-8ad4-e2e75c7bd2c8@z3g2000prd.googlegroups.com...
> 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 

0
Don
11/19/2009 10:01:37 PM
On Nov 19, 2:01=A0pm, "Don Guillett" <dguille...@austin.rr.com> wrote:
> This assumes that there are NO duplicates that are NOT "zeroed out"
>
> Sub delnulifiedrows()
> mc =3D 1 ' col A
> For i =3D Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
> If Cells(i - 1, mc) =3D Cells(i, mc) Then Rows(i - 1).Resize(2).Delete
> Next i
> End Sub
>
> =A0 =A0 =A0 Part# =A0 Quantity Value
> =A0 =A0 =A0 013-002 =A0 =A05 25
> =A0 =A0 =A0 022-001 =A0 =A012 17
> =A0 =A0 =A0 052-061 =A0 =A0 10 30
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@austin.rr.com"Tracey" <xinxindong2...@gmail.com> wrote in mess=
age
>
> news:07d81668-cee7-41a8-8ad4-e2e75c7bd2c8@z3g2000prd.googlegroups.com...
>
>
>
> > 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# =A0 Quantity Value
> > 013-001 =A0 =A01 =A0 =A0 =A0 =A0$1.00
> > 013-001 =A0 =A0-1 =A0 =A0 =A0 $1.00-
> > 013-002 =A0 =A05 =A0 =A0 =A0 =A0$25.00
> > 013-007 =A0 =A03 =A0 =A0 =A0 =A0 $120.00
> > 013-007 =A0 =A0-3 =A0 =A0 =A0 =A0$120.00-
> > 022-001 =A0 =A012 =A0 =A0 =A0 =A0$17.00
> > 041-009 =A0 =A0 7 =A0 =A0 =A0 =A0 $251.00
> > 041-009 =A0 =A0 -7 =A0 =A0 =A0 =A0$251.00-
> > 052-061 =A0 =A0 10 =A0 =A0 =A0 $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- Hide quoted text -
>
> - 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
0
Tracey
11/19/2009 10:34:32 PM
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
dguillett1@austin.rr.com
"Tracey" <xinxindong2004@gmail.com> wrote in message 
news:f19f0b9f-af6b-4e7e-a2e8-4f1b4d92a7ed@u8g2000prd.googlegroups.com...
On Nov 19, 2:01 pm, "Don Guillett" <dguille...@austin.rr.com> wrote:
> 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
> dguille...@austin.rr.com"Tracey" <xinxindong2...@gmail.com> wrote in 
> message
>
> news:07d81668-cee7-41a8-8ad4-e2e75c7bd2c8@z3g2000prd.googlegroups.com...
>
>
>
> > 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- Hide quoted text -
>
> - 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 

0
Don
11/19/2009 11:08:41 PM
Reply:

Similar Artilces:

How to shift address listings from row list to columns?
I have a mailing list with name, address, city, state & zip with each item in individual rows like a list of labels and a few empty rows of space between each listing. How do I create/transfer this list into columns accross so I can sort by city or zip? Thank you! If your data is nicely grouped, with each group in 5 lines, viz: name add city state zip then an earlier suggestion given which worked might be worth a try: See: http://tinyurl.com/wgcb -- hth Max ----------------------------------------- Please reply in newsgroup Use xdemechanik <at>yahoo<dot>com for email --...

Excluding hidden columns and rows when copying to another workbook
When I print part of a worksheet that has hidden columns and rows - the hidden columns and rows do not print. That's what I want. Now--I'd like to take that same data and copy it to another workbook excluding the formulas and hidden columns and rows so that the new file contains only the data as was printed. How can I do that? PJ Select your range including hidden rows and columns then Edit>Go To>Special>Visible cells only>OK Now do your copy/paste. Gord Dibben Excel MVP On Fri, 6 Feb 2004 10:16:07 -0800, "PJ" <anonymous@discussions.microsoft.com> wr...

numbering rows
Is there a way to number each row in Excel so that when it prints out the rows all have numbers? I couldn't find it in Msft online Help but I guess that's no surprise. thanks! Jane Two options. 1. In page setup>sheet select "row and column headers". Note this will print the column headsers also. 2. Insert a column to left of your original columns(will become column A) Enter in A1 =ROW() and drag/copy down the column as far as you wish. Gord Dibben Excel MVP On Fri, 23 Apr 2004 17:15:10 -0700, "jane" <jfnysf@hotmail.com> wrote: >Is there a ...

retain only rows with condition that cells in column H containing "AU"
I have a large database containing columns with one column specifies the product name. In one instances, I need to retain only rows with condition that cells in column H containing "AU" of the entire string in the cell. Delete those rows without it. The problem is , that the AU of the string can appear in any position, not a fixed position. Is there any simple way of doing it? Many Thanks! Regards, Bora Hi, You can use AutoFilter. Choose the command Data, Filter, AutoFilter. Then open the autofilter drop down in the column H and choose Custom and from the first drop down, t...

validation list or combo box dependant on cell value
Am i able to determine the values shown in either a validation list or combo box being dependant on a value in another cell? ie: Cell A1 = BOB then validation or combo box would then base it's list from the named range (or whatever the solution may be) based on Bob. if i was to change A1 to ROY then it would also change the underlying list? I have tried everything that i think SHOULD work but that it pretty limited... thanks in advance rich I'd start with Debra Dalgleish's site: http://contextures.com/xlDataVal02.html Richard Edwards wrote: > > Am i able to deter...

copy rows to another file
Dear Experts I have following code,i need to copy desirde rows to new file (r.xls) on sheet 1. This code is able to copy desired rows from active sheet to sheet2(same file) based on values in column G. Would you please guide me? regards Sub Marine() Dim arrParts() As String Dim MyRange As Range, CopyRange As Range Dim LastRow As Long LastRow = Cells(Cells.Rows.Count, "G").End(xlUp).Row Set MyRange = Range("G1:G" & LastRow) response = InputBox("Enter rows to copy in the format nnn,nnn,nn") arrParts = Split(response, ",") For Each ...

how to compare 2 values in a report (Invoice Total vs Payments)
while running a report how would I set a message "out of balance" if my invoice amount (Table 1 ) does not equal the value of my total payments (table 2). If the values were the same then no message would be printed. Thank You Create a new command button to check the report before you print it. You will have to enter code for the button along the lines of: If [Invoice Total] <> [Payments] Then MsgBox "This account is out of balance" Endif "Rita" wrote: > while running a report how would I set a message "out of balance" if > my invo...

how to delete Microsoft office
How can i delete microsoft office from my computer. i downloaded the demo but i don't want it anymore. Thank you "Dany" <Dany@discussions.microsoft.com> wrote in message news:B2B2C1A8-2A65-4E97-BE3B-EC2DC2D42107@microsoft.com... > How can i delete microsoft office from my computer. i downloaded the demo > but > i don't want it anymore. Thank you Control Panel, Add and Remove Programs, Microsoft Office, Remove. Dany Or if you cannot as Jeff posted then see the below http://support.microsoft.com/kb/928218 -- Peter Please Reply to ...

if value not found in table ?
Hello all I need to display a msgbox if a value is not found in a table. Something like: If value not_in table.field then msgbix end if I know that code wont work is just an ilustration of what i am looking for Thanks in advance Use DLookup() to see if the value is in the table. If it's not found, the result will be Null. So, use IsNull() to test the result. Here's how to get your Dlookup() expression working: Getting a value from a table: DLookup() at: http://allenbrowne.com/casu-07.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access us...

Hiding Column and Row Bars.
I know theres a way to costumize the way you view an excel page by hiding toolbars, but is there a way to hide the rows and column bars just so all you can see is the actual page. E.G. is there a way to Hide the top bar that defines the colums "A", "B", "C","D" and Rows 1,2,3,4,5. Go to Tools>Options>View, there are a number of options you can play with there. -- HTH RP (remove nothere from the email address if mailing direct) "tamato43" <tamato43@discussions.microsoft.com> wrote in message news:7D1E5DEB-D6F2-47C8-95E2-2...

automatically numbering rows
I wish to have rows automatically numbered in column "A" according to the content in column "B". If column "B" has text or numbers in a cell I do not want to number that row in column "A". If column "B" is blank in a cell I want to number that row in column "A". I found the following formula that works the opposite of what I want "=IF(B1<>"",COUNTA($B$1:B1)&".","")" . What would the formula be if I wanted to numerically count the blank cells in "B" and skip the conte...

Sorting Data Automatically
I am importing text into a new worksheet and would like to run a macro that can perform labour a labour intensive sort/deletion. Column A contains a mixture of text strings as follows: QR4567 QR4567/QT1223 QR4290 QR4290/QT1224 What I would like to do is determine how many QR's have associated QT's and how many QR's are remaining that dont have associated QT's. Any assistance would be greatly appreciated. Alan Bartley Sydney Australia Hi Alan not sure how the sorting comes into this - sounds more like needing a formula to count when a cell contains both QR & QT as op...

Help on adding values to a bar chart
Hello, Here is my problem. I currently have a bar chart that has 8 X-axis bars. I need to add 2 more to the X-axis. The labels for the x-axis are based on cells within another spreadsheet, but are not consecutive cells, they are spread apart. So when I go to add the two more cells, I can only add one. When I click on the second cell to add it to the list, all of the previous cells get unselected. I have even tried to manually add these two values in. Is there a limit to how many values can be on the X-axis? Please Help!!! Thanks. To select non-consecutive cells: select first cell, h...

Populating work sheet combox with another work sheet values
Hi All, I have a combox in my sheet(1) which i want to populate with values present in sheet(2). I can populate combox in sheet(1) by setting the "ListFillrange"property to the required ranges in the sheet(1) (assume in coulmn A i have values from A1: A5) But i am unable to populate same combox with sheet(2) values(assume in sheet(2) C column i have set of values from C1: C10). I want to populate without using any macros. Can anyone Please help me to solve this problem Thanks in advance -- sjayar ------------------------------------------------------------------------ sj...

supplier info gets deleted
I have run a report for items in a category in order to organize them into the proper categories & departments I double click the item look up - properties opens - I change the deparment &/or category - OK When I look at these items again - the supplier information is gone - I have to reopen each one and re-enter the supplier! Can anyone tell me why & how to avoid this happening? ALSO in my category report I am finding some suppliers blank - but when I open the properties the supplier is there - I have to delete the supplier & re-add the supplier. Anyone know why &am...

How to merge columns and rows into one cell besides using Merge and Center Icon?
I had posted this question before, but I couldn�t find this thread in any of the pages up till page 17. So I�m posting again. I want to merge all rows and columns starting from A1 to J2, with no lines in between into one cell. My text data value are in D1 and D2 respectively. Using Merge and Center Icon will only retain the upper-left most data, resulting the data in D2 to be deleted. So how to merge all rows and columns into one cell and yet prevent the data in D2 to be deleted? Any help will be greatly appreciated. ------------------------------------------------ ~~ Message posted from h...

deleting rows #2
i have200 rows in sheet- there are lets say 100 rows the title shipping i have another 100 rows with the title payments. they are in mixed in and not grouped together otherwise i could highlight them all together and delete-how do i delete all the ones that say shipping without clicking on each individual row. I am on excel 2007 trial cb wrote: > i have200 rows in sheet- there are lets say 100 rows the title shipping > i have another 100 rows with the title payments. > they are in mixed in and not grouped together otherwise i could highlight > them all together and delete-how d...

deleting duplicate value in columns
What is the best method to delete rows containing dulicate (same number) numbers in a column (Excel 2000)? The workbook has over 30,000 rows which makes it impractical to sort and then delete the duplicates page by page. Thanks in advance. Hi Jim have a look at http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows Frank Jim Brokenshire wrote: > What is the best method to delete rows containing dulicate (same > number) numbers in a column (Excel 2000)? The workbook has over > 30,000 rows which makes it impractical to sort and then delete the > duplicates page b...

Outlook 2007 Outlook.exe deleting itself
I seem to be having a very strange problem. When I start Outlook 2007, it stalls and then deletes the Outlook.exe file. This is a relatively new problem (within the last week). All other files seem to be present. If I keep a full copy of Outlook.ext in another folder, that is also deleted. In some cases, after restoring the file, Outlook will start to come up but then stall and die. In other cases, it won't won't even start to come up but just deletes itself. I've run several different types of anti-virus software including AVG, Avira, Spybot and none seem to find an issue or he...

Cond. Formatting depending of value in another sheet
Hi all, I've tried to do conditional formatting whether the cellvalue i another sheet is greater or smaller than the cellvalue I have in th active cell. I've tried to do "formula is" IF(B34<Sheet1!B34)and format, but it won work. Any help is apprecciated!! //Thoma -- Message posted from http://www.ExcelForum.com Hi Try: =B34<Sheet1!B34 -- Andy. "Jonsson >" <<Jonsson.1635tc@excelforum-nospam.com> wrote in message news:Jonsson.1635tc@excelforum-nospam.com... > Hi all, > > I've tried to do conditional formatting whether the ...

Warning off by deleting a sheet
Hi Is there away to set warning off when i deleting a sheet in Code Sorry i have found the solution "alvin Kuiper" skrev: > Hi > Is there away to set warning off when i deleting a sheet in Code > ...

Deleting data from a table through a query
I have a database that is designed to update a list of credit union members from a master list so that vehicle insurance coverage can be tracked. I have been able to run an unmatched query to achieve a list of old members who have either paid off their vehicles or moved their loans to other locations. What I need to do now is delete these people from the main table. I have the cascade update and delete funtion in place to delete the vehicle information once the member is deleted, but I don't know how to take the information found in the unmatched query and delete those members fr...

SQL Server row limit of 8000 bytes
hi when i want add a new field in contact i have this error : dmLog: Failed to add new String attribute (CFSl_importance) to Contact entity. and dmLog: New size of the attribute ({00027E4A-174C-4D15-AF43-C64CF39E0BA4}) exceeds the SQL Server row limit of 8000 bytes. is someone know why? th u mathias This would be very helpful since I ran into the same problem where I was able to create a customization file, but couldn't import it on a different system. However, where can I find this on Partnersource? I can't find the article, nor can I find any fix releases. Is there a pla...

CRM API: Converting an Object Type value -> ObjectType
I've setup post callouts in CRM which notify me when a CRM object has been modified. CRM sends me the ObjectType value so I know what type of object has been modified. For example 1 for an Account, 132 for an Activity... I can't figure out however how to convert the number back into an ObjectType. In theory it should be very simple, but from what I can tell the ObjectType class is NOT an Enum, oddly enough. I've tried the following: int objectTypeValue = 2; // which is a Contact // returns otActivity test = (ObjectType) Enum.ToObject(typeof(ObjectType), objectTypeValue); // r...

how to create leading zeros on variable length numbers
I have a column of variable length numbers/text (6-12 characters). I need to place leading zeros in each of the cells to create a standard length of 15 characters. What's the easiest way to do this? sleect cells or column then pull-down: Format > Cells... > Number > Custom > and enter 000000000000000 in place of general -- Gary's Student "MVPitts" wrote: > I have a column of variable length numbers/text (6-12 characters). I need to > place leading zeros in each of the cells to create a standard length of 15 > characters. What's the easie...