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:
Best Answer
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,"")
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.
No, but set it up this way, and Tab and Shift+Tab will take you to the next and previous highlighted row. First do this:
Change your formula to =IF(MID($C2,5,1)=" ", 1, "") (copying down, of course)
Highlight the column (Ctrl+Space will do that if you're in any cell in that column).
Press F5, then Alt+Special, Formula, de-select Text, then Enter
To exclude errors, de-select Errors in step 3 also. (e.g., when LEN($C2)<5, but you could also change the formula to account for that)
Now the Tab key will step you through each cell in that column that has a numeric value in it (1). It also has the added bonus that in step 2 above, you can see the total of the highlighted rows in the status bar at the bottom of the screen (Sum:).
Best Answer
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,"")
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.