Eliminating blank cells

[PointerMan]

How do I eliminate the blank cells in my worksheet so that everything
essentially shifts to the left until the empty cells are gone?

I want this: To look like this:
A _ 5 1 _ 3 1 _ A 5 1 3 1
B 9 _ _ 1 2 1 9 B 9 1 2 1 9
C 3 1 2 _ _ 2 8 C 3 1 2 2 8
D _ _ 6 1 3 _ 2 D 6 1 3 2
* The _ represents the empty cells

Each value would still be in it's own cell, and the blanks would be gone.

 

[Mike H]

Hi,

Select the range of data then

Edit|Goto - Special
select blanks
OK

Right click one of the selected cells and click
delete
select - "Shift cells left"
OK

Mike

 

[Max]

Select the range, then press F5 > Special > Check "Blanks" > OK
Right-click on the selection > Delete > Check "Shift Cells Left" > OK
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik

 

[PointerMan]

This isn't working for me. Nothing happens when I select "blanks" and hit
"OK". After that I right click on a cell and delete and shift cells left,
and it only shifts that row left by one cell. What am I missing?

 

[Gord Dibben]

Select the range then...........

F5>Special>Blanks>OK

Edit>Delete>Shift cells left

What do the letters A though D in Column A represent?


Gord Dibben MS Excel MVP

 

[David Biddulph]

Are you sure that you'd selected the range of cells before you did the
">> Edit|Goto - Special
Are you sure that you've actually got blank cells, and that they don't, for
example, have spaces in them?

 

[PointerMan]

I checked a few of the cells, and I don't see anything in them. I also
selected the range of cells, so I'm not sure what's going on.

 

[David Biddulph]

Although you can't see anything in them, have you tried =ISBLANK(B1) if you
think B1 is blank?
A zero length text string, such as from the formula ="", still doesn't show
as blank so wouldn't be selected by theoperations.

 

[Gord Dibben]

Could be the cells have a space or two in them and are not blank.

Check with a helper cell =LEN(cellref)


Gord

 

[David Biddulph]

And even if there is no space, a zero length string ="" would not be found
by Goto/ Special/ Blanks,
so ISBLANK may be a safer check.

 

[Max]

I use this sub (by Jay) to clear cells with residual zero length null
strings: "" within a selected range

Just select the range, run the sub
Then perform the earlier steps given in our responses
(Try it out on a spare copy)

Sub ClearNulls()
Set rng = Selection
For Each ar In Selection.Areas
For Each itm In ar
If Trim(itm.Value) = "" _
Then itm.ClearContents
Next 'itm
Next 'ar
End Sub

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---