MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Deleting Alternating Blank Rows

October 05, 2017 - by Bill Jelen

Deleting Alternating Blank Rows

Every other row in Excel is blank. How can you quickly delete the blank rows?

Watch Video

  • Quickbooks loves to insert tiny column between the columns
  • To speed up the process, delete first column, then use F4 for Fast Re-Do
  • Better: Select a row. Go to Special, Blanks, Ctrl + -, Entire Column, OK
  • Thanks to Carmella in Green Bay

Video Transcript

Learn Excel from MrExcel, Podcast Episode 2153: Deleting Blank Columns.

Hey. I was in Green Bay, Wisconsin, doing a seminar there and the question came up about these tiny blank columns between the columns and is there fast way to get rid of those, and, well, yeah, sure. I mean, the way that I always talk about doing this is to delete the first one however you want. I use ALT+E, D, C, OK, but then, from there, it’s easy. Just move to the next blank column. Press F4. F4 is the world's greatest redo -- repeats the last command you did -- and now it's just simple F4, RIGHT ARROW, F4, RIGHT ARROW, F4.

You know, hey, if you have forty of those, that's going to be a hassle to do that, and, in the audience, was Carmella, said, oh hey, wait, no. There's a fast way to do that. Just choose the whole row. When you choose the whole row, and then we're going to use FIND & SELECT, GO TO SPECIAL. Now, I'm going to do this with keyboard shortcuts, so CONTROL+G for GO TO, ALT+S for Special, K for BLANKS. That chooses all of the blank cells in that row. Click OK. Alright, so, now, we've selected all those blanks. Now it's a simple matter to delete, so CONTROL – sign, press C for ENTIRE COLUMN, OK, and, bam, you're done. What an awesome trick.

Now, Carmella gave me a tip here. Make sure that you choose a row that everything's filled in. If you had something where there was data but something wasn't filled in, well then, that wouldn't work at all, but a great, clever, clever way to solve that problem.

Tips like that, in my book Power Excel With MrExel. Click the i on the top right-hand corner.

Today’s episode: QuickBooks loves to insert those tiny columns between the columns; to speed up the process of deleting them, delete the first column, then use F4 for a fast redo; but better, thanks to Carmella in Green Bay, select a row, go to SPECIAL, BLANKS, CONTROL – sign, choose C for ENTIRE COLUMN. Okay.

Hey. I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.

Download File

Download the sample file here: Podcast2153.xlsm

Title Photo: Pixabay

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.