Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Rahim Kassam
 
Posts: n/a
Default 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.


  #2   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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 ,
"Rahim Kassam" 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 2 May 13th 23 11:46 AM
How do I convert an existing MS Excel worksheet tracking a simple. [email protected] Excel Discussion (Misc queries) 0 January 4th 05 01:53 AM
Macro in Excel 2002 to save a workbook to a FTP location Lloyd Excel Discussion (Misc queries) 0 December 21st 04 03:49 PM
excel macro inconsistency JM Excel Discussion (Misc queries) 2 December 9th 04 02:13 AM
Excel user desires to learn ABC of Access Hari Excel Discussion (Misc queries) 1 December 3rd 04 03:32 AM


All times are GMT +1. The time now is 10:11 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"