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. |
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. |
All times are GMT +1. The time now is 08:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com