Making Game: Excel, Delete blank rows without shifting data

Original Source Link

I want to delete blank rows, without shifting the data in anyway.

F5, Special, Blanks doesn’t work as the cells as that deletes all blanks. Also doing a filter for blanks does not work.

Any other workarounds?

Blue is current, red is expected:

enter image description here

You can make it easier with a helper column.

Use e.g. this formula to mark rows with content:

enter image description here

Now you can select your helper column, F5 – special – check “formula” and unmark “number”, this selects all the empty rows.
Just right click on one of them and delete – entire row.

  1. Highlight rows you wish to delete

enter image description here

  1. Right click rows and select Delete

enter image description here

This should completely delete any rows you wish to remove and move the cells up accordingly.


You can Hide these rows if it makes it any easier for you to manage without changing the Rows #’s of the other cells.

  1. Highlight rows you wish to hide by clicking the row number

  2. Right click the highlight rows and select Hide

enter image description here

Alakazam! They should now be out of your way

enter image description here

Keep in mind, this does not remove the rows entirely. You can Unhide them by right clicking the small sliver of where the rows were previously.

Let me know if that helps!

The way I’ve been doing things like this ‘forever’ is to add a couple of columns, sort, delete all the rows I don’t want (as one block), then sort again, back to the original sort.

Add columns A and B, where A is incrementing and B is concatentation of C, D and E.

enter image description here
enter image description here

The formulas need to be made into unchanging values, so use copy, paste values:
enter image description here
enter image description here

Then sort by column B, which brings all the blanks together:
enter image description here
enter image description here

Once you’ve deleted the blanks, it’s in the wrong order, but you just sort again, this time by column A:
enter image description here
enter image description here

There is a purpose-built solution to your question within Excel.

Select columns A, B, and C. Go to the ‘Data’ ribbon. In the ‘Data Tools’ section, there is the option to “Remove Duplicates”. Click ‘OK’.

This can be achieved with the shortcuts:

Alt then A then M then Tab then Tab then Enter.

Two caveats:

  • This technique will leave the “first” blank row intact.
  • All non-blank duplicated rows will also be removed. Therefore, this solution is not ideal if there are intentional duplicates.

The method I’ve used is to highlight the rows with no data (by ctrl-clicking the row numbers) and then deleting the entire selected row range (which should “shift up” rows below the deleted sections). Likewise, if you’ve empty columns, you can do the same and “shift left” any columns to the right. This will adjust relative cell references in your formulae, but absolute cell refs (row and column refs with dollar signs) will likely break. So keep that in mind.

This is a manual process, and may not answer your question, since it appears you’re looking for a one-shot method to consolidate a selected range.

Tagged :

Leave a Reply

Your email address will not be published. Required fields are marked *