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

Excel Zoom

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


Remove Spaces From Empty Cells

February 5, 2009 by Mark 3 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. Cells that may appear empty really aren’t, as they will often times contain spaces. Usually this isn’t a problem, but sometimes pivot tables or other features in Excel will read the space as a value, when it really should be read as 0.

The macro below will take whatever worksheet you’re on and will remove all the spaces from the otherwise aesthetically empty cells. It won’t remove spaces from cells that contain text, formulas, numbers, etc. 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 had a space prior to running the macro and notice that it doesn’t now.

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.

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

Search this site…

Categories

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



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

Login Form

Lost your password?