+ Reply to Thread
Results 1 to 3 of 3

New Excel user needs help with simple Macro...

  1. #1
    Rahim Kassam
    Guest

    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. #2
    JE McGimpsey
    Guest

    Re: New Excel user needs help with simple Macro...

    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 <[email protected]>,
    "Rahim Kassam" <[email protected]> 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.


  3. #3
    JE McGimpsey
    Guest

    Re: New Excel user needs help with simple Macro...

    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 <[email protected]>,
    "Rahim Kassam" <[email protected]> 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1