Prepare for the show: A framework for hiding rows and columns in your spreadsheet applications

Prepare for the Show

Hiding a bunch of rows and columns in a sheet before showing it to your boss is inevitable.  I insert blank rows and columns around a table, so I can use the CurrentRegion property of the Range Object in my code. I add labels to all my named ranges in the sheet. I split out complex formulas into a couple of columns. Ultimately I end up with a lot of rows and columns to hide.  I desperately needed a framework to hide and un-hide rows and columns in all my sheets. I experimented with a lot of methods before settling down with one and I would definitely like to know if you have a better way to do it.

Continue reading “Prepare for the show: A framework for hiding rows and columns in your spreadsheet applications”

To Err is Excel, Handle your Errors with grace


Error handling is an important aspect of programming in VBA, especially if you are writing macros for other users. Unfortunately, many users ignore it completely. Visual Basics is an amazing programming language, but it lags far behind in the error handling department. All we have is the On Error”, “Goto” and the “Resume” statements. These statements allow only a few error handling structures, and each of the structures has its own set of expert proponents. In this post, I am going to share with you, a little block of code that I use to handle errors in all my spreadsheet applications; and hopefully offer a fresh perspective.

Continue reading “To Err is Excel, Handle your Errors with grace”