Eliminating blank cells

P

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.
 
M

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
 
P

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?
 
G

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
 
D

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?
 
P

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.
 
D

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.
 
G

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
 
D

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.
 
M

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
---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top