Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    May 2005
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    deleting 0's (Excel 2003)

    I have created this macro to remove cells containing 0's. However if the cell has formula and the cell is showing 0, it does not recognize it. Could not figure out what is wrong with the macro.

    Thanks in advance
    Aloys

    Sub ClearSumZero()
    Dim NumRng As Range
    Dim cell As Range
    On Error GoTo ErrorMsg
    Set NumRng = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants , 1)
    For Each cell In Intersect(Selection, NumRng)
    If cell.HasFormula = True And cell.value = 0 Or cell.Value = 0 Then
    cell.ClearContents
    End If
    Next
    Exit Sub
    ErrorMsg:
    MsgBox "Nothing to clear!"
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: deleting 0's (Excel 2003)

    Using ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, 1) means that you only look at cells containing a constant numeric value. Cells containing formulas are ignored.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: deleting 0's (Excel 2003)

    If you do not want 0's to display on the sheet you can simply choose TOOLS | OPTIONS and switch to the VIEW TAB. Deselect the ZERO VALUES option. This causes all 0's to not display on that sheet!
    Regards,
    Rudi

  4. #4
    New Lounger
    Join Date
    May 2005
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: deleting 0's (Excel 2003)

    Hans,

    I have taken .SpecialCells(xlCellTypeConstants, 1) out and it works perfectly!!!!

    Kinda of a newbie on VB.

    Thanks a million!
    Aloys

  5. #5
    Star Lounger
    Join Date
    Jun 2005
    Location
    Singapore
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: deleting 0's (Excel 2003)

    Hi Aloys,


    I used your codes but don't seem to work at clearing those zeros. pls enlighten me.

    cheers, kun

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: deleting 0's (Excel 2003)

    Aloys' purpose was to clear all cells within the selection whose value is 0, whether as a constant or as the result of a formula. The code as posted originally will only clear constant values, not zeros that are the result of a formula. The following version will clear both:

    Sub ClearZeros()
    Dim cell As Range
    On Error GoTo ErrorMsg
    For Each cell In Intersect(ActiveSheet.UsedRange, Selection)
    If cell.Value = 0 Then
    cell.ClearContents
    End If
    Next cell
    Exit Sub
    ErrorMsg:
    MsgBox "Nothing to clear!"
    End Sub

    Note: by clearing a cell, you remove its formula, so the value won't change any more, even if the cells that contributed to the result of the formula change.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: deleting 0's (Excel 2003)

    First, you have to modify Aloys' code to something like this:

    <code>
    Sub ClearSumZero()
    Dim oCell As Range
    On Error GoTo ErrorMsg
    For Each oCell In Selection
    If oCell.Value = 0 Then
    oCell.ClearContents
    End If
    Next oCell
    Exit Sub
    ErrorMsg:
    MsgBox "Nothing to clear!"
    End Sub
    </code>



    Seocnd, you need to select all of the cells that you want the macro to work on, then run the macro. The macro will find all cells that contain a formula that evaluates to zero or a constant zero and delete the contents of the cell.
    Legare Coleman

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: deleting 0's (Excel 2003)

    Unless I am missing the point and Aloysicus wanted the cells cleared of its content, this will also work...it simply hides the display of zeros in all cess on the sheet!

    Sub HideAllZeroOccurences()
    ActiveWindow.DisplayZeros = False
    End Sub
    Regards,
    Rudi

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: deleting 0's (Excel 2003)

    Aloys will have to tell us what he wanted...

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: deleting 0's (Excel 2003)

    The way you structured the message "MsgBox "Nothing to clear!"...sounds as if the Macro is not clear of what to do. Aloy will run the macro and if there is no zeros to clear the macro says .... "Hmmn...I'm confused and not clear on what to do!!" <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Regards,
    Rudi

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: deleting 0's (Excel 2003)

    No, that would be: "MsgBox "Nothing too clear!"
    Legare Coleman

  12. #12
    New Lounger
    Join Date
    May 2005
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: deleting 0's (Excel 2003)

    Han's method will help clear the 0's within a range/selection of cells....that is the purpose of the macro.

    Hope it helps, Kun

Posting Permissions

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