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.

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


"PointerMan" wrote:

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.

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" wrote:
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.

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?


"Mike H" wrote:

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


"PointerMan" wrote:

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.

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

F5SpecialBlanksOK

EditDeleteShift cells left

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


Gord Dibben MS Excel MVP


On Tue, 30 Dec 2008 12:33:33 -0800, PointerMan
wrote:

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.

Are you sure that you'd selected the range of cells before you did the
" Edit|Goto - Special
select blanks
OK" ?

Are you sure that you've actually got blank cells, and that they don't, for
example, have spaces in them?
--
David Biddulph

"PointerMan" wrote in message
...
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?


"Mike H" wrote:

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


"PointerMan" wrote:

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.

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" wrote:

Are you sure that you'd selected the range of cells before you did the
" Edit|Goto - Special
select blanks
OK" ?

Are you sure that you've actually got blank cells, and that they don't, for
example, have spaces in them?
--
David Biddulph

"PointerMan" wrote in message
...
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?


"Mike H" wrote:

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


"PointerMan" wrote:

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.

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 the
Edit|Goto - Special
select blanks
OK
operations.
--
David Biddulph

"PointerMan" wrote in message
...
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" wrote:

Are you sure that you'd selected the range of cells before you did the
" Edit|Goto - Special
select blanks
OK" ?

Are you sure that you've actually got blank cells, and that they don't,
for
example, have spaces in them?
--
David Biddulph

"PointerMan" wrote in message
...
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?


"Mike H" wrote:

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


"PointerMan" wrote:

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.

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

Check with a helper cell =LEN(cellref)


Gord


On Tue, 30 Dec 2008 13:21:42 -0800, PointerMan
wrote:

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" wrote:

Are you sure that you'd selected the range of cells before you did the
" Edit|Goto - Special
select blanks
OK" ?

Are you sure that you've actually got blank cells, and that they don't, for
example, have spaces in them?
--
David Biddulph

"PointerMan" wrote in message
...
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?


"Mike H" wrote:

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


"PointerMan" wrote:

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.

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.
--
David Biddulph

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Could be the cells have a space or two in them and are not blank.

Check with a helper cell =LEN(cellref)


Gord


On Tue, 30 Dec 2008 13:21:42 -0800, PointerMan
wrote:

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" wrote:

Are you sure that you'd selected the range of cells before you did the
" Edit|Goto - Special
select blanks
OK" ?

Are you sure that you've actually got blank cells, and that they don't,
for
example, have spaces in them?
--
David Biddulph

"PointerMan" wrote in message
...
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?


"Mike H" wrote:

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


"PointerMan" wrote:

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.

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