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:
You can make it easier with a helper column.
Use e.g. this formula to mark rows with content:
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.
- Highlight rows you wish to delete
- Right click rows and select
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.
Highlight rows you wish to hide by clicking the row number
Right click the highlight rows and select
Alakazam! They should now be out of your way
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.
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.
- 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.