• Blog
  • Excel Downloads
    • Audit Tickmark Toolbar
  • Courses
  • Contact

Excel Zoom

...because it's more than just a calculator


Add Spaces to Empty Cells

February 5, 2009 by Mark 5 Comments

We put information from all types of sources into our Excel workbooks, usually just by copying and pasting or by using another program to extract data from a database. In either case, sometimes this produces undesired results. Empty cells could play tricks on certain features in Excel, such as pivot tables, that read an empty cell as 0. If we don’t want these cells to appear in the pivot table, a simple space is all you need to keep it from messing up your data.

To use this macro, select your data, and then run the macro. If your list of data is large, give the macro a couple seconds to run, as it will evaluate each cell in your list. It won’t remove data from cells that contain text, formulas, numbers, etc. It will only add a space to cells that are truly blank. After running this macro successfully, you really shouldn’t notice a visual difference in your spreadsheet. You’ll only notice a change if you know that a particular cell did not have a space in it prior to running the macro and notice that it does after running it.

Copy all of the code below. Paste it into your workbook’s Visual Basic editor, in a Module, or Microsoft Excel Object (This Workbook, Sheet1, Sheet2, etc.).

Need help? Use our nifty guide to help figure out how to install and use your macros.

You must be logged in and have an active membership to view this content. Please register or login to continue.

 

You must be logged in and have an active membership to view this content. Please register or login to continue.

Filed Under: Macros Tagged With: Formats, Macros

Looking For More Help?

Contact us with any specific questions or feedback. We love to hear from you!

Want to level up your career? See our amazing Certificated Excel Courses and be the office Hero with your new found knowledge!

Looking for the Excel Audit Tickmark Toolbar? click here, NOW ONLY $25!

Subscribe to our mailing list
  • Facebook
  • Google+
  • Twitter

Search this site…

Categories

  • Macros (34)
  • Tips (67)
  • Uncategorized (23)



Copyright © 2021 · Magazine Pro Theme on Genesis Framework · WordPress · Log in

Login Form

Lost your password?