Auto row deletion

undefined :mad: I open a csv file into excel and it is sorted into
columns.  two of the columns have values in them.  If both of the
columns, for each row,  have 0.00 as the value I want to delete that
row,  there are usually a lot of these rows and I don't want to have to
manually go down a list
of 100+ rows and delete the rows where there are two zero values. How
can I automate this ?


-- 
rogeraw
------------------------------------------------------------------------
rogeraw's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19964
View this thread: http://www.excelforum.com/showthread.php?threadid=388316

0
7/19/2005 12:39:52 PM
excel 39879 articles. 2 followers. Follow

2 Replies
383 Views

Similar Articles

[PageSpeed] 45

The quick-n-dirty answer - no error checking/etc but should get you started.
Change Cells(row,1) to Range("A"&row) if prefer.
Either way, presumed data being checked are in columns A and B so correct as
needed.

Sub mad()
'manipulate a copy of the data
Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "Print"
'
finalrow = Range("A65536").End(xlUp).Row
'
For Row = 2 To finalrow
If Cells(Row, 1).Value = 0 And _
   Cells(Row, 2).Value = 0 Then
       Cells(Row, 1).EntireRow.Delete
End If
Next
End Sub

"rogeraw" <rogeraw.1sf42a_1121778305.0304@excelforum-nospam.com> wrote in
message news:rogeraw.1sf42a_1121778305.0304@excelforum-nospam.com...
>
> undefined :mad: I open a csv file into excel and it is sorted into
> columns.  two of the columns have values in them.  If both of the
> columns, for each row,  have 0.00 as the value I want to delete that
> row,  there are usually a lot of these rows and I don't want to have to
> manually go down a list
> of 100+ rows and delete the rows where there are two zero values. How
> can I automate this ?
>
>
> --
> rogeraw
> ------------------------------------------------------------------------
> rogeraw's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=19964
> View this thread: http://www.excelforum.com/showthread.php?threadid=388316
>


0
Jefgorbach (42)
7/19/2005 2:13:57 PM
To Jef G.,

Thanks for reply but your solution does not work and last time I
programmed was in 1-2-3 in the late 80's so I cannot modify. 

The zero values are in columns C & D so if on any row the
value in cols C and D are 0.00 I want the row deleting.

Please revisit the macro,  thanks.

Roger


-- 
rogeraw
------------------------------------------------------------------------
rogeraw's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19964
View this thread: http://www.excelforum.com/showthread.php?threadid=388316

0
7/29/2005 4:16:08 PM
Reply:

Similar Artilces:

referencing another sheet and using auto fill
Hi there. I'm trying to create a summary sheet to summarise data from 12 sheets (jan to dec). I have 84 lines each with a member of staff and I need to produce a table with the totals of thei leave in each month. Each seperate month sheet has 84 lines and a total for each person already on it. I need to end up with something like this on the summary sheet: jan feb mar etc total peter's leave totals for month and year: 3 4 1 8 john's leave totals for month and year: 7 2 3 ...

Auto fill cells with dates, exclude weekends?
What I'm doing is filling in multiple cells with dates (by dragging and filling) automatically. I was wondering if there was anyway for it to skip the weekends within the dates? Thanks in advance. Hi Sam rather than dragging with the left mouse button down, drag with the right .... when you let go you'll see an option for fill weekdays. Cheers JulieD "Sam Weber" <sam@hostradius.com> wrote in message news:af3901c4796d$85dff660$a501280a@phx.gbl... > What I'm doing is filling in multiple cells with dates > (by dragging and filling) automatically. I was wo...

auto-completion of addresses in Outlook 2002-SP2
Recently had to make new profiles for one of my satellite offices due to a server crash. Now in Outlook 2002, they have lost the auto-complete history in Outlook. Is there a place where this is located and can be moved over to the new profile rather than having them re-create as they go? The AutoComplete cache is stored in a file with a .NK2 extension. If you're using Windows 2000 or XP, you'll need to search in hidden and system folders to find the .NK2 file. -- Jocelyn Fiorello MVP - Outlook *** Replies sent to my e-mail address will probably not be answered -- please rep...

delete command
In my subform, in the AfterUpdate of the field Quantity, i have the folowing code: If Me!size > 19 Then MsgBox " this is a drum. Please choose a drum" DoCmd.GoToControl "productid" DoCmd.Beep Exit Sub End If How can i delete any figures a i have entered in the field quantity? For example i have entered 2,i get the message that i am wrong, but before Exit i want to delete any figures,in my case 2, from the field Quantity ? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200708/1 What could the code you've po...

How do I auto sum on a form in publisher
I am trying to create a form that would auto sum columns like Excel. I can't figure it out! About the only way is to insert the Excel form as an Object. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Dawn" <Dawn@discussions.microsoft.com> wrote in message news:CD9B3D05-A0C5-4095-AB55-BC627D57DB50@microsoft.com... >I am trying to create a form that would auto sum columns like Excel. I can't > figure it out! ...

Showing Duplicate Rows
Hi All, I have seen the question with that same title however that is not exactly "showing" duplicates. It is more showing unique (not duplicate) I would like to truly show the duplicates. For instance there are several duplicate product numbers that are in my sheet and I want to display all the duplicates so that I can make corrections. IE: Item number 31323 31324 31325 31326 31326 31327 31328 31328 31329 31330 31331 How can I filter so that excel will only show those rows that have duplicate Item numbers? Thanks Eagle Use data>filter>autofilter>filter by column...

'07 Large Operation Error (Delete / Insert Rows and/or Columns)
Help! I was so excited to have '07 until... I have a very simple worksheet in a work book - This very simple worksheet takes up 78 rows and 16 columns. Minimals formats / formulas etc. I would like to insert additional rows but I get the error message which says "Large Operation. The operation you are about to perform affects a large # of cells and may take a significant amount of time to complete. Are you sure you want to continue? Note: This operation will be performed in 60 seconds if no response is received". I have tried to select "OK" as well a...

Subform..Deleting a Record by code
I have a Before Update event to Trap an Entry of "SN" (which is not acceptable) and results in a Msgbox and "Cancel = True". If the User backspaces the "SN" leaving a Null, and leaves the Record, how can I delete that record by code? TIA - Bob "Bob Barnes" <BobBarnes@discussions.microsoft.com> wrote in message news:46A5C7AA-F19F-477E-938A-D99EE9CBF4DC@microsoft.com... > I have a Before Update event to Trap an Entry of "SN" (which is not > acceptable) and results in a Msgbox and "Cancel = True". > > If the U...

Auto Printing of attachments
Hi If a mail lands in my inbox can I auto-print an attachment if it has one? I know how to auto-print the email body itself through the Rules wizard but I want to actually print the attachment only. I've got M$ Outlook 2003 Cheers John There's an addin here that may help: http://www.sperrysoftware.com/Outlook/Auto-Print.asp "John Wright" <vacuum_forming@hotmail.com> wrote in message news:%23kQb%237WkHHA.1340@TK2MSFTNGP04.phx.gbl... > Hi > > If a mail lands in my inbox can I auto-print an attachment if it has one? > > I know how to auto-print t...

HOW DO CANCEL/DELETE message from Newsgroup server??
Once email is sent, it cannot be deleted before reachign recipient's address (unless you own server at recipients' end which is extremely highly unlikely) So just as with spoken word, be careful and dont' allow emotional rushes to result in spontaneous email you may regret later. But my question is about Usenet/Newsgroups (NNTP protocol): Is there a way to cancel/delete you rown posted message, at leas tbefore any followups appear (or even after)? As you all know, most of us hate Web Forums and we remain loyal to Usenet. But I have to admit, there's ONE fea...

delete or void unposted cash receipts using econnect 8.0
I need to delete or void unposted cash receipts exist in table RM10201 using econnect. The class which is provided in econnect to void documents “taRMVoidTransaction” works on table RM20101 only which is for posted documents. ...

increase total rows
Hi, can someone show me how to increase the maximum number of rows, more than 65,536? thanks Stan I presume you have Excel 2007? Open your .xls file in Excel 2007, save the file as an Excel 2007 file type (.xlsw, .xlsx, etc) using File Save As. Close the file and reopen it you should now have 2007's 1M+ rows available. "Stan" wrote: > Hi, > can someone show me how to increase the maximum number of rows, more than > 65,536? > thanks > Stan Thanks!!! "JLatham" wrote: > I presume you have Excel 2007? > > Open ...

Exchante 2000 deleted public folder
Using Exchange 2000 a client deleted a public folder called say AAA by mistake. It staff member. Therefore the folder had an email address of AAA@xxx.nsw.gov.au. We found an old PST and tried under exchange admin to recreate the folder. It kept saying email address already exists. Found under AD users/computers advanced the Exchange folder and found the object for this folder. How do we recreate the folder using the same email address. This email address is published on our website so we cannot change it. Urgent any hep accepted -- Kath On Tue, 30 Nov 2004 23:41:03 -0800, Kaddi...

cannot delete inbox messages
When deleting messages from the inbox they go to the deleted folder but will not delete from the inbox folder. I cannot find any information or any setting that will change this. Please let me know what direction I should take. Rob <Rob@discussions.microsoft.com> wrote: > When deleting messages from the inbox they go to the deleted folder > but will not delete from the inbox folder. I cannot find any > information or any setting that will change this. Please let me know > what direction I should take. So, you're saying that you delete an item from the inbox and ...

email by rows
hello All: I'm looking for a macro that will go down a sheet and look for th email address and mail every thing in that row to *ONLY* that person n matter if it has 10 columns or 20 columns I have a sheet set up lik this Employee Name LAB Degree Section E-Mail Address paper1 paper2 paper3 I will be adding more columns as the year goes on that is why it nee to be able to send different size rows. Any help would be appreciated Dal -- Message posted from http://www.ExcelForum.com Hi DaleL Which mail program do you use -- Regards Ron de Bruin http://www.rondebruin.nl "D...

How do I insert a row of blank cells?
I need to know how to insert a row of blank cells every other row in the columns from F to I ONLY!!! I currently have just a straight set of data in those columns like data-data-data-data-data-data. I need to have it alternate data-blankrow-data-blankrow-data-blankrow- as I go down from row to row. I need to do this for about 1000 rows so I need a quick way to do it if there is one. HELP!!! Looks like this: Data Data Data Data I want this: Data Data Data Data Thanks. Chris -open the VB editor -double click the sheet of interest -View from the menu--> Code -Paste the belo...

Help w/ Auto Accept Agent
Hi Everyone. I am installing the Auto Accept Agent in order to facilitate scheduling conference rooms for meetings. This is the first time I've ever used this plug-in. I've created the user account, conference.room@ourdomain.com, and am ready to download the Windows Installer Package. There are a couple of things I am unclear about and want to understand before I proceed. First, when I run the .msi, one of the questions asked is to "Run as Local System" or "This user"? Can anybody explain the difference and when I would use each one? Second, to register th...

deleting extraneous toolbar buttons in Toolbar Editor?
Okay, this is the first time I've added a toolbar to a dialog-based application. When inadvertently resizing the bitmaps, this became interpreted as adding a button. Is there a way I can delete extra buttons without recreating the ones I want to save/use? Thanks. Try the delete key after selecting the button or simply dragg them off the toolbar. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Jim" <Jim@discussions.microsoft.com> wrote in message news:F61A6C02-57F2-44EE-BC16-ABFE9D581D22@microsoft.com... > Okay, this is the first time I've added a toolba...

Macro to shift all source data in a chart by one row or column?
Hello all, I have a spreadsheet which contains at least twenty years of data and twenty or thirty charts. Each year we update all of the charts to move the forecast period to the current year and the next five years. Because there is data well beyond the five year period, a dynamic range for the chart data would not work. Can anyone suggest if it is possible to write a macro which I can run on each chart to shift the source data for each series in the chart by one column or row to the right? Any suggestions would be gratefully received! Thanks in advance, Shan try this defined ...

Auto Response #2
Hi, My forest is abc.com and I have a child pqr.abc.com . Due to some reasons we are planning to remove pqr.abc.com domain from the forest and creating a new domain named test.com My requirement is Can I have some solution with a auto mail response.The senders who sends the email to @pqr.abc.com should get a automated notification email saying that the email address is changed @test.com. Thanks -- Vinod Kumar - Since you didn't mention what version of Exchange you're on, Exchange Server 2007 will let you do this using Transport Rules, on the Edge or on the Hub (if you'...

Grouping consecutive rows/columns in different groups
Hello, I am trying to use the group command under the data menu: I want to have one group for rows 1 -5 and a different group for row 6-10. Everytime i group 1-5 then i try to group 6-10, it creates on group of rows 1-10??? How do i keep these groupings seperate -- Message posted from http://www.ExcelForum.com HI, I'm not sure this is best, but if you insert a line between row 5 and 6, then group, they'll be separate. jeff >-----Original Message----- >Hello, >I am trying to use the group command under the data menu: >I want to have one group for rows 1 -5 and a differ...

Counting rows that fit within a range
I've got what seems to be a simple problem to do by hand (if it weren't for the fact that the table in question isn't exactly small). So I was hoping I could do it (somehow) in an Excel spreadsheet. What I have, is a column of numbers (to one decimal point), assumed to be sorted in descending order. I want to have another column (calculated by Excel) that will count how many rows have a larger value (in the first column) than it, but only up to a certain range (by adding a constant to the value of that row). For example, here's some data, with the first column supplied by ...

How to increase max. Row count in an Excel sheet?
Is there any way to increase the max row count in Excel 2000? No. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Adarsh Sharma" <adarsh_4u@hotmail.com> wrote in message news:591e01c42d46$d3788c70$a501280a@phx.gbl... > Is there any way to increase the max row count in Excel > 2000? > Hi no way -- Regards Frank Kabel Frankfurt, Germany Adarsh Sharma wrote: > Is there any way to increase the max row count in Excel > 2000? ...

TOP 3 in row source
Hello In the row source of a listbox I have SELECT TOP 3 ... but it has no effect / displays more than 3 rows. Is TOP not valid in row sources? How can I get around this? I am ultimately trying to make the number of rows returned to the listbox to be variable based on a number which I calculate. I tried entering the constant 3 and it did not work. Robert Top three based on what? What is the entire SQL statement for the row source? Top will return a distinct number, but if the value you are using to sort on has matches, then it will return all the records that match the 3rd v...

Find Message that was auto-archived
Hello, How can I find a message that I sent a year ago and has been auto-archived. Thanks in advance. JP jfp2@verizon.net open the archive.pst using file, open, outlook data file and use advanced find to search the archive pst. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Search for answers: http://groups.google.com Most recent posts to the Outlook newsgroups: http://groups.google.com/groups?as_ugroup=microsoft.public.outlook.*&num=30 &qu...