Did you spend too much time to move cells so that there are no blank cells in each column?

Again, a picture tells thousand words.  Here’s the task…

Would you do it this way?

Or this way?

What did I do in the second animated screenshot?

In short, what you need to do is to select all blank cells, delete them and shift cells up. That’s all.

Step 1: Select the range (Shortcut: Ctrl+*) 

Step 2: Go to  –> Special… –> Blank (Shortcuts: Ctrl+G, Alt+S, k, enter)

Step 3: Right Click on a blank cell selected –> Delete… (Shortcut: Ctrl+–)

Step 4: Select Shift cells up –> OK

As simple as that!

Note:  Shift cells up applies to whole columns…… Make sure there is nothing underneath your range selected, otherwise you may distort everything below. 😦

Read also Clear or Delete.

If you do have something underneath, you may consider

1. move everything below out of the columns affected; and then move them back;
2. copy the data range (that you need to shift cells up) to a blank sheet, apply the steps above and then copy back to replace old;
3. VBA? which i have no idea. 😛