Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Remove Zeros

  1. #1
    Join Date
    9th June 2006
    Posts
    35

    Remove Zeros

    I'm sure this is really easy, and I'm just loosing my mind but: I need a macro that will remove all zero values from a set range in multiple tabs. I don't want to delete the cell, row or column - just take the "0" out...I've tried this:
    VB:
    Sub remove_zeros() 
        r = 119 'my first row
        mySheets = Array("ACC", _ 
        "BUS", _ 
        "CEO", _ 
        "COM", _ 
        "CSD", _ 
        "DUN", _ 
        "EDI", _ 
        "FAC", _ 
        "FIN", _ 
        "HAL", _ 
        "HR", _ 
        "IT", _ 
        "PP", _ 
        "SPS", _ 
        "MVM", _ 
        "DCA") 
        Do 
            If ActiveCell.Value = "0" Then 
                mySheets.Cells(r, 1).Delete 
                r = r + 1 
            End If 
        Loop Until r = 174 'my last row
    End Sub 
    
    
    but it isn't working the way I thought it would. HELP?

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    Re: Remove Zero Values

    Why not simply use Edit>Replace and replace 0 and leave the replace with empty? Just Group your needed Worksheets 1st, then select the range and use Replace.

  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    Re: Remove Zero Values

    Or, if it's only a visual thing, try

    If you don't like the Zeros showing you can hide them via Tools>Options>View - Zero values. Or, cell-by-cell with a Custom Format like: General;-General;

  4. #4
    Join Date
    9th June 2006
    Posts
    35

    Re: Remove Zeros

    I would do the replace, but then any 10's, 100's or 1,000's would be messed up...

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

  6. #6
    Join Date
    9th June 2006
    Posts
    35

    Re: Remove Zeros

    DOH! It was a late night last night and SUCH a Monday today - thanks for that

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Remove Zeros

    .. here is the code..

    VB:
    Dim mySheets, r As Range 
    mySheets = Array("ACC", "BUS", "CEO", "COM", "CSD", "DUN", "EDI", _ 
    "FAC", "FIN", "HAL", "HR", "IT", "PP", "SPS", "MVM", "DCA") 
     
    For i = 0 To UBound(mySheets) 
        Set r = Sheets(mySheets(i)).Range("a119:a174") 
        With r 
            .Replace What:="0", Replacement:="", LookAt:=xlWhole, _ 
            SearchOrder:=xlByRows, MatchCase:=False 
        End With 
    Next 
    
    

  8. #8
    Join Date
    9th June 2006
    Posts
    35

    Re: Remove Zeros

    That's super nice of you to post for me, Krishnakumar! Thanks

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 13
    Last Post: March 6th, 2008, 08:19
  2. Remove Preceeding Zeros
    By h in forum Excel General
    Replies: 14
    Last Post: July 8th, 2007, 16:38
  3. Remove Front Zeros & Add Space Before Last 2 Characters
    By Belinda Li in forum Excel General
    Replies: 6
    Last Post: June 18th, 2007, 23:20
  4. Macro: Remove Zeros From Array
    By Anders Lund in forum Excel General
    Replies: 13
    Last Post: December 20th, 2006, 23:46
  5. Access: VBA remove Part Numbers with varying 2 to 6 zeros in
    By alienscript in forum Excel and/or Access Help
    Replies: 2
    Last Post: January 10th, 2004, 05:17

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
  •  
porno