9

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

Improve this question
2
  • 1
    What do you mean “without shifting data”?  Your example shows “Over” being shifted from Row 12 to Row 2, “Flow” being shifted from Row 13 to Row 3, “Super” being shifted from Row 22 to Row 4, etc. — not to mention all the data being shifted from Columns A-C to Columns H-J.
    – G-Man Says 'Reinstate Monica'
    Apr 20, 2017 at 6:52
  • please don't add "Excel" to the title. It's the job of the tag
    – phuclv
    Feb 7, 2021 at 6:31

5 Answers 5

Reset to default
7

You can make it easier with a helper column.

Use e.g. this formula to mark rows with content:
=IF(COUNTA(A2:C2)>0,1,"")

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.

Improve this answer
2
  • 1
    Yes this is definitely what I was looking for, thanks!
    – Jonnyboi
    Mar 24, 2017 at 18:33
  • @Jonnyboi This level of work is not required - a simpler solution is available, purpose-built by Microsoft. See my answer.
    – Grade 'Eh' Bacon
    Mar 24, 2017 at 19:30
4
  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.

**Extra:**

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!

Improve this answer
6
  • thanks Cheesus, this would work, but how can i do this for many rows? It may take awhile to do just row by row.
    – Jonnyboi
    Mar 24, 2017 at 18:10
  • If you click the first row, hold Shift and click the last row. It will highlight all the rows in between. If they are separated by data, you could hold Ctrl and click to select multiple rows one at a time.
    – Cheesus Crust
    Mar 24, 2017 at 18:11
  • 2
    Maybe this is something for VBA, i have 2000 rows! might take awhile.
    – Jonnyboi
    Mar 24, 2017 at 18:12
  • 2
    Export to a CSV list, and then you can programmatically filter out sequences that are nothing but commas, using script language of choice.
    – George Erhard
    Mar 24, 2017 at 18:14
  • 2
    @GeorgeErhard That's a decent solution, but it wouldn't work if there's any formatting on the table, it'd just lose the formatting.
    – Tyzoid
    Mar 24, 2017 at 18:49
4

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

Improve this answer
3

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.
Improve this answer
1
  • It works in case no duplicate Data especially in column C
    – yass
    Mar 24, 2017 at 20:05
1

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .