+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : deleting blank (or zero) cells in macro

  1. #1
    Registered User
    Join Date
    10-13-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    deleting blank (or zero) cells in macro

    I am just learning about Macro's and although I don’t find them too hard I cannot figure something out.

    I have several worksheets that I am combining to one master sheet. I am intentionally including blank rows because if I add to the individual worksheet info I want them to show. The blank rows return as 0 in the cells, I have tried to write a macro to delete these cells. I have tried to update them as blanks but still no luck.

    I also wonder (because I have not been able to test it) if when I do update the blank cell to include info if that blank cell will then show up in the list and not be deleted.

    Sorry for so many questions. Thank you in advance for your help!!!

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: deleting blank (or zero) cells in macro

    Once the compilation is complete:

    1) turn on the DATA > FILTER > AUTOFILTER
    2) do a filter on one of the columns for BLANKS or for 0
    3) Delete all the rows left visible
    4) Turn off the Autofilter


    That can be added to your existing macro pretty easily.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    10-13-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: deleting blank (or zero) cells in macro

    Thank you! I was hoping for something so that when I run my macro to combine the sheets it would automatically take out the blank rows so that if someone else (like my boss) runs it the sheet would be ready to filter. Anyone have additional suggestions?

  4. #4
    Registered User
    Join Date
    10-06-2010
    Location
    America
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: deleting blank (or zero) cells in macro

    If you delete the cells, your info won't be linked and will not show up. I get around this by simply making a custom format. Set the custom format to #. (actually type in the pound sign) This will make all of the empty cells blank instead of showing 0. It has no effect on actual numbers and will not truncate them to one character so I find it is the best workaround of which I can think.

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: deleting blank (or zero) cells in macro

    We have no idea how you're creating your combined sheet, if we saw that and the other macros you're using, perhaps we could suggest something more specific for you.

    Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

+ 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