Hello Excellers. Today’s handy Excel Macro is one use an a regular basis.. I receive a really large data dump every week, and I have to filter out blank records based on Column B then delete them. Well there has to be a quick simple way to do this with code right? Right!!. A few lines of VBA and we are done!.
What Does The Macro Do?
The Macro deletes an entire row if the corresponding cell is Column B is blank.
How Does It Work?
We use the Range.SpecialCells method, which returns a range object that represents all cells that match the value and and type we specify. In this case we are looking to return a range object which returns all blank cells in Column B.
Let’s get coding!
First we need to open Visual Basic – by hitting ALT +F11 or Developer Tab – Visual Basic – Insert Menu – Module. You can either save this Macro in your current workbook, or choose to save this your Personal Macro Workbook for use at any time on any workbook.
Step 1. First we turn off error handling in Excel. This enables the Macro to continue running even if it comes across an error in its execution. In a lager project, this would not be advised as we would want to know detail about errors, but in such a small piece of code, it just allows it to run without error.
Step 2. We specify that in the Active Worksheet Column B, any blanks cells are identified using xlBanks, and the rows that contains the blank cells is deleted entirely.
Step 3. Test Your Macro”!. (I told you it was a small piece of code right?)
Want To Copy The Code?
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
You can also download the corresponding example workbook that contains the Macro code by clicking on the download button below.