New Excel user needs help with simple Macro...

Hello,

I am relatively need to Microsoft Excel and I need help creating a simple
Macro.

I would like to create a Macro that will:

- take the value of the selected value
- add it to the value of the cell below it
- if the sum of these two cells is zero -  replace values of both cells with
an x
- move to the next set of cells in the same column repeat the procedure
- continue the procedure for the entire column
- once that is done, for each cell with an x delete the entire row

the reason why I do not wish to delelte the rows on the fly is because it is
possible that after a deletion of a pair, the value of the cell above it and
the value of the cell below could also sum to zero but I would want to keep
these.

I have tried to accomplish this with the use of the Macro recorder and VBA
in Excel 2000 but have been thus far unsuccessful, any help would be much
appreciated.

Thanks in advance.

R.


0
1/24/2005 3:33:39 AM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
528 Views

Similar Articles

[PageSpeed] 10

If I understand you correctly:

    Public Sub DeleteSumToZeros()
        Const dEpsilon As Double = 1e-10
        Dim rCell As Range
        Dim rDelete As Range
        For Each rCell In Range("A1:A" & _
                Range("A" & Rows.Count).End(xlUp).Row - 1)
            With rCell.Resize(2, 1)
                If .Cells(1) + .Cells(2) < dEpsilon Then
                    If rDelete Is Nothing Then
                        Set rDelete = .Cells
                    Else
                        Set rDelete = Union(rDelete, .Cells)
                    End If
                End If
            End With
        Next rCell
        If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
    End Sub

Note: I used

    If .Cells(1) + .Cells(2) < dEpsilon Then

rather than

    If .Cells(1) = -.Cells(2) Then

to avoid missing a match due to small rounding errors, assuming that the 
cells are calculated rather than directly entered. Adjust dEpsilon as 
necessary



In article <X9OdnYsJAOAM8WncRVn-jg@rogers.com>,
 "Rahim Kassam" <rahim.kassam@rogers.com> wrote:

> Hello,
> 
> I am relatively need to Microsoft Excel and I need help creating a simple
> Macro.
> 
> I would like to create a Macro that will:
> 
> - take the value of the selected value
> - add it to the value of the cell below it
> - if the sum of these two cells is zero -  replace values of both cells with
> an x
> - move to the next set of cells in the same column repeat the procedure
> - continue the procedure for the entire column
> - once that is done, for each cell with an x delete the entire row
> 
> the reason why I do not wish to delelte the rows on the fly is because it is
> possible that after a deletion of a pair, the value of the cell above it and
> the value of the cell below could also sum to zero but I would want to keep
> these.
> 
> I have tried to accomplish this with the use of the Macro recorder and VBA
> in Excel 2000 but have been thus far unsuccessful, any help would be much
> appreciated.
> 
> Thanks in advance.
> 
> R.
0
jemcgimpsey (6723)
1/24/2005 2:10:15 PM
Reply:

Similar Artilces:

How do I subtotal pages in Excel?
I want to subtotal a column per page in Excel 2000, however the page may vary as I add and delete records. Can anyone help me? Shouldn't be a problem as XL will automatically adjust it's total formula as you add and delete. I'm assuming you are putting in a sum formula ("=sum( cell1:cell2)"). -- Regards; Rob ------------------------------------------------------------------------ "lshivvers" <lshivvers@discussions.microsoft.com> wrote in message news:B81870EF-D160-4628-96A2-3CDC5FB07BBD@microsoft.com... > I want to subtotal a column per page in Ex...

HQ users should be able to add ship-to records from the stores.
HQ users should be able to add ship-to records from the stores. As of RMS 1.3.???, HQ users can indeed create ship-to records at the stores. I'm told that RMS doesn't handle this properly. There are problems created when stores are messing around with global customers. I suspect that MS will 'fix' this by eliminating the ability of stores to add Ship-To records. I have a customer, ABC Fitness, who has one office, but several gyms. I repair the treadmills at their various gym locations. I have each service site set up as a Ship-To in RMS, within the ABC global customer ac...

Opening a unicode CSV file from Excel
Hi, I'm currently working on an application that can export data in "CSV" format, and then launches Excel to view the CSV data. This works perfectly ok for ascii data. However now I have upgraded our application to use Unicode I'm running into problems. When I export data into a unicode CSV file and launch excel with it's path on the command line I get a "This is not a recognized format" message. If the same file is opened manually from Excel the same error is shown but afterwards the text import wizard is displayed (which works ok). Currenly the file is 16 ...

Excel #81
I need to know the importance of excel and what are the uses of excel. Your responce in this matter will be highly appreciated. Regards Qinisile There's lots of information on Excel on the Microsoft site: http://office.microsoft.com/en-ca/FX010858001033.aspx Qinisile Bukhali wrote: > I need to know the importance of excel and what are the > uses of excel. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

create folder to all users
i am searching for a way to create a folder called search and replay under the outbox folder. i do not wish to use a plublic folder. the search and replay folder will open up to a web page. i need to know if there is any way of creating this folder without going to each of the workstations. we are running exchange 2003 w/ mixed outlook versions (200,xp,2003). please offer me any ideas you might have. thank you for your help. You'll have to script a solution. Nothing native in Exchange will let you do this and deploy to existing users. Nue "murr" <martynaughton@gmail....

Must buy new computer...Ideas?
Hello to all! Finally my over 10-1/2 year old Dell desktop's extreme slowness has seriously gotten on my nerves and I'm now looking for a new one. (Surprisingly, it's working pretty well otherwise...) Maybe this isn't the right forum? But beside the usual things like RAM and HD GBs, are there things I should be sure to have...or be careful NOT to have on a desktop? After n ot looking into computers for over a decade I feel like a Martian in the Earth. lol! Also, is it possible to transfer to the new one ALL my stuff, including programs, etc.? Do you thin...

Default properties for new folders
How does one set the default properties applied when creating new email folders? Currently, when I create a new folder, it is created with the AutoPreview turned on by default. I'd like to change that do that it is left off by default. It's something of a pain to create a new folder and then go in to customize it's properties. You can do this by editing the Messages view; View-> Arrange By-> Current View-> Define Views... You might want to define a new view yourself and apply that view on first use of that folder. -- Robert Sparnaaij [MVP-Outlook] www.howto-o...

need help converting worksheet formula to vba
right now i have this formula in cell A75. It searches for "1" and i found shows "1" in cell A75 =IF(SUMPRODUCT(ISNUMBER(SEARCH("1",$A$12:$A$72))+0)>0=TRUE,1,0) I have this formula in B75. it sums all cells in col. I that has the in col. A =SUMIF($A$13:$A$72,A75,$I$13:$I$72) I am trying to convert this to VBA, this is what I have tried. Set SourceWB = Workbooks("book1.xls") With SourceWB.Sheets("sheet1") I am getting "Sub or function not defined" and "search" is highlited. Range("a75") Application.Works...

Help with setup
Running Windows XP Professional and Office XP Professional. Since I installed SP2 I can't seem to get Messenger 6.2 and Outlook to work correctly. Is this possible or do I need to uninstall Messenger 6.2 and use Windows Messenger. I use POP 3 email in Outlook. Messenger is setup to sign in with my hotmail account. I have Ethernet 10/100 network with 2 additional PC's that I am going to hook up although they are not hooked up yet. I access the internet thru dial up modem. I assigned static IP to network card and setup Outlook to use dialup modem to access email. I also hav...

Excel charts #12
I am trying to format the individual labels on the category axis on an Excel chart. I want every other label in a different color and bold. But the entire category axis is formatted. Does anyone have a suggestion? The category axis is years - 1990 through 2005. I want the odd years to be bold and a different color than the even years. When Excel doesn't let me do what I want with a chart axis, I draw my own: http://peltiertech.com/Excel/Charts/ArbitraryAxis.html Use this technique to make two different axes, one for even and one for odd, and hide the default axis Excel draw...

Excel memory issues
We have several excel spreadsheets on a network drive that when opened by any client they receive several errors relating to memory, such as: Not enough memory Not enough system resources to display completely Any ideas? We have increased RAM on server and workstation and page file, but the users are still receiving the errors. Did you increase the memory on the client's machine? You never mentioned if they have enough harddrive space. "Neil Shirley" wrote: > We have several excel spreadsheets on a network drive that when opened by > any client they receive s...

Need to Transfer Files Between Money and Quicken
I've got an interesting problem here. I need to transfer files between Money 2002 and Quicken Premium 2004. The first four attempts at having Quicken read exported QIF files has been a disaster. Quicken is absolutely no help. Their built-in help is referring me to webpages that don't exist. There is supposed to be a tool to convert the file format, but the fine print says it is for converting between previous versions of Quicken and Quicken 2004, but it "may work" for converting Money files. A Quicken guru says I need to export Money files to XML in order to bring the files i...

New Email Messages
I just installed Outlook 2007 on my new PC and for some reason the new messages I received can only be received when I press the SEND/RECEIVE BUTTON. My Outlook at work receives these new mail messages as they are received. So how can I configure the system to make that happen? -- The Trail Hiker!! There are two settings within the accounts 1) Send on starting 2) Auto send/receive every xx minutes - set this for 10 minutes "hiker0531" <hiker0531@aol.com> wrote in message news:87259C7A-5C07-4846-BF61-DE3F98345D21@microsoft.com... >I just installed Outlook 2007 on my n...

User Calendar Vanished
Hello, Just a quick query regarding a user whos calendar contents seems to have vanished! for dates prior to June 05 This is not a big deal, but i thought a little odd.. any reason anyone might know of why this would happen? one thing i suggest was that some data archived off has been deleted.. do archives include calendar data ? thanks On Tue, 21 Feb 2006 01:44:28 -0800, "Chris" <Chris@discussions.microsoft.com> wrote: >Hello, > >Just a quick query regarding a user whos calendar contents seems to have >vanished! for dates prior to June 05 > >This is...

Unable to open "New" window to write email.
I am using Microsoft Outlook in Office 2003. It has been working great until today. When I click "New" to write a new email I get an error window : The messaging interface has returned an unknown error. If the problem persist, Restart Outlook. I have tried this several times, even restarted the computer to no avail. Anyone have a solution???? ...

Prevent new comments on old posts in sharepoint blogs
How do I stop comments on certain posts in a SharePoint blog? I have removed permissions to the specific post to the lowest possible access and people can STILL post a comment. If I uncheck one more item in the list of permissions, the users can no longer see the post. Any suggestions? ...

excel spreadsheet #8
I have a 2 sheet excel spread sheet that I put together with varing column widths. On the top 1/4 of the first sheet (of the 2 part spread sheet) I need to have different column widths to insert specific data with different column widths that conflict with the column widths in the balance of spread sheet. How do I seperate the sheet to have the column with at the top 1/4 differ from those on the rest of the sheet? I treied cut & paste within excel, I tried building the top part in word and paste to excel. nothing works. Can this be done? Frank ...

Make excel run large spreadsheets faster
For those of you who run large spreadsheets with or without VBA, I think everyone will agree they are much too slow ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=e1105194-24af-4697-ab5f-b9d62e5140ea&...

New Location/New setup
Currently I'm downsizing our office, and moving to a new location. I'm running MS Server2003 as well as Exchange 2003. Once moved, I will no longer require the server (since I am setting up a home office). Are there currently any steps or procedures available to correctly shutdown one office...and basically setup a home office with no exchange server. Also I will be removing the current pc's from the Domain, and setting up a few in a workgroup. I would like to receive emails from the other employees for time being until I completely cancel this email account. I already have a...

A Short Video on Adding/Editing New Customers
If capturing customers at the POS is important to you, take a couple of minutes and watch this video. This application also allows for edit of Global customer information in HQ installations. http://www.retail-pos.com/ReverseLookup.wmv Contact us for detailed info and demo. sales(at)retail-pos.com www.retail-pos.com It would be nice if you could encode the videos with a more common codec, and not in wmv format. This requires the GTM3 codec (GoToMeeting). Windows Media Player 11 doesn't automatically download the codec, and I'm not going through the trouble to download and in...

Excel
I need to insert a space three characters from the right of a piece of text I have in a cell. How do I do it? I was thinking along the lines of customer formatting but this is proving troublesome as I'm an Excel novice. Thanks, Darren If your piece of text is in cell A1, you could put the following formula into a helper cell (such as B1): =LEFT(A1,3)&" "&RIGHT(A1,LEN(A1)-3). If you want to convert the formula back into text, click on B1, press Ctrl+C to copy it, then click on Edit | Paste Special | Paste: Values | OK. "Dazza" wrote: > I need to in...

How do I activate the new mail button
When I start Outlook, the "New", "forward", "reply" and "reply to all" buttons are not active which means I can not send mail. I have no problems receiving mail. Also, on web pages with email links, when I click on them, the Outlook application is not launched which is almost certainly related to this. I think I'm missing something very simple here! Are you in a mail enabled folder? Do you have an active email account installed? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert...

excel #136
I have files in quantrum pro and would like to open them in excel. But they won't popen I'm not familiar with Quantrum Pro. What does it do? "Liz R" wrote: > I have files in quantrum pro and would like to open them in excel. But they > won't popen ...

Excel 2007 werkbalk snelle toegang, "hoe te saven voor nieuwe excel install "
Wie kan mij vertellen hoe de werkbalk "snelle toegang"met al zijn icons is te saven. Dit ivm een nieuwe installatie van excel. Het voorkomt dat ik al die icons wederom moet opzoeken. Al vast bedank voor de hulp. gr Peter See http://www.rondebruin.nl/imageqat.htm You can find the file here Excel saves your QAT setup in a file named "Excel.qat" in: C:\Documents and Settings\<username>\Local Settings\Application Data\Microsoft\OFFICE\Excel.qat In Vista you can find it here C:\Users\<username>\AppData\Local\Microsoft\Office\Excel.qat Note: This file will no...

Style available all sheets new or old
I need a custom style that is available in all spreadsheets, new or old. I can set style and save it in book.xlt in xlstart, but that only works for new workbooks, and doesn't work with old spreadsheets when I call them up. I want to be able to select a group of cells, or column or row and apply a setting for negative numbers to have brackets. I am using Excel 2000. I even tried saving the style in personal.xls, but that doesn't work as it is hidden. Do I have to use a macro? And if so what would it be? Thanks One way: Public Sub MergeStyles() ActiveWorkbook....