I have sheets with data in adjoining columns. Not every cell has data. I want
to removed all the empty cells and leave only a full page of data. At the
moment the only way I can think of is advance filtering for non-blanks but I
have to do this for each individual column which is time consuming (multiple
sheets etc)
Alternatively would there be a way of filtering all the columns for
specific words and returning only cells that meet certain criteria

Damien,

Select columns, then use Edit / Go To... Special Blanks OK, then Rt-Click any of the blank
cells, then choose Delete / Entire Row OK.

HTH,
Bernie
MS Excel MVP


"DamienO" wrote in message
...
I have sheets with data in adjoining columns. Not every cell has data. I want
to removed all the empty cells and leave only a full page of data. At the
moment the only way I can think of is advance filtering for non-blanks but I
have to do this for each individual column which is time consuming (multiple
sheets etc)
Alternatively would there be a way of filtering all the columns for
specific words and returning only cells that meet certain criteria

On Jun 20, 11:37 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Damien,

Select columns, then use Edit / Go To... Special Blanks OK, then Rt-Click any of the blank
cells, then choose Delete / Entire Row OK.

HTH,
Bernie
MS Excel MVP

"DamienO" wrote in message

...

I have sheets with data in adjoining columns. Not every cell has data. I want
to removed all the empty cells and leave only a full page of data. At the
moment the only way I can think of is advance filtering for non-blanks but I
have to do this for each individual column which is time consuming (multiple
sheets etc)
Alternatively would there be a way of filtering all the columns for
specific words and returning only cells that meet certain criteria

What if C10 is blank but D10 has data? How are you planning on
deleting just blank cells if the whole row isn't blank?

I guess I read it too quickly - after using the go to part, rt-click and choose Delete /
Shift-cells up OR Shift cells left then press OK.

HTH,
Bernie
MS Excel MVP


What if C10 is blank but D10 has data? How are you planning on
deleting just blank cells if the whole row isn't blank?

Damien,

Select columns, then use Edit / Go To... Special Blanks OK, then Rt-Click any of the blank
cells, then choose Delete / Entire Row OK.

HTH,
Bernie
MS Excel MVP

"DamienO" wrote in message

...

I have sheets with data in adjoining columns. Not every cell has data. I want
to removed all the empty cells and leave only a full page of data. At the
moment the only way I can think of is advance filtering for non-blanks but I
have to do this for each individual column which is time consuming (multiple
sheets etc)
Alternatively would there be a way of filtering all the columns for
specific words and returning only cells that meet certain criteria

Hi Bernie,
The process worked fine up to the rt-click part.
It only gives me the option to delete entire row or entire column. Either
option deletes information

"Bernie Deitrick" wrote:

I guess I read it too quickly - after using the go to part, rt-click and choose Delete /
Shift-cells up OR Shift cells left then press OK.

HTH,
Bernie
MS Excel MVP


What if C10 is blank but D10 has data? How are you planning on
deleting just blank cells if the whole row isn't blank?

Damien,

Select columns, then use Edit / Go To... Special Blanks OK, then Rt-Click any of the blank
cells, then choose Delete / Entire Row OK.

HTH,
Bernie
MS Excel MVP

"DamienO" wrote in message

...

I have sheets with data in adjoining columns. Not every cell has data. I want
to removed all the empty cells and leave only a full page of data. At the
moment the only way I can think of is advance filtering for non-blanks but I
have to do this for each individual column which is time consuming (multiple
sheets etc)
Alternatively would there be a way of filtering all the columns for
specific words and returning only cells that meet certain criteria

On Jun 23, 4:21 am, DamienO wrote:
Hi Bernie,
The process worked fine up to the rt-click part.
It only gives me the option to delete entire row or entire column. Either
option deletes information


That's kinda my point. Is each row a set of information? Just because
there might be a blank cell in the set of info doesn't mean that's
wrong. Example: If it's an address, some may have suite/apartment
numbers and others won't, so you'd want a blank cell for them.

If the cells going across don't relate to each other (each column is
distinct data not related to other columns), you can just do a Sort to
get rid of blanks. Do a sort on each column by itself and the blanks
will just go to the bottom and "vanish".

Spiky,
Its still a lot of columns to sort (about 80 per tab and 16 - 20 tabs in the
workbook)I was looking for a quicker way to 'condense' all of the cells with
information into one area. What I have at the moment is like a checker board
of information in cells. The info doesn't relate across columns so I only
need to remove any blank cells to be left with data.


"Spiky" wrote:

On Jun 23, 4:21 am, DamienO wrote:
Hi Bernie,
The process worked fine up to the rt-click part.
It only gives me the option to delete entire row or entire column. Either
option deletes information


That's kinda my point. Is each row a set of information? Just because
there might be a blank cell in the set of info doesn't mean that's
wrong. Example: If it's an address, some may have suite/apartment
numbers and others won't, so you'd want a blank cell for them.

If the cells going across don't relate to each other (each column is
distinct data not related to other columns), you can just do a Sort to
get rid of blanks. Do a sort on each column by itself and the blanks
will just go to the bottom and "vanish".

Damien,

I'm not sure why, since I get four options when I right click on any of the
selected blanks cells... I'm using XL2003, though I don't recall it being
any different in earlier versions, and I have never tried it in XL2007.

Bernie


"DamienO" wrote in message
...
Hi Bernie,
The process worked fine up to the rt-click part.
It only gives me the option to delete entire row or entire column. Either
option deletes information

"Bernie Deitrick" wrote:

I guess I read it too quickly - after using the go to part, rt-click and
choose Delete /
Shift-cells up OR Shift cells left then press OK.

HTH,
Bernie
MS Excel MVP


What if C10 is blank but D10 has data? How are you planning on
deleting just blank cells if the whole row isn't blank?

Damien,

Select columns, then use Edit / Go To... Special Blanks OK, then
Rt-Click any of the blank
cells, then choose Delete / Entire Row OK.

HTH,
Bernie
MS Excel MVP

"DamienO" wrote in message

...

I have sheets with data in adjoining columns. Not every cell has
data. I want
to removed all the empty cells and leave only a full page of data.
At the
moment the only way I can think of is advance filtering for
non-blanks but I
have to do this for each individual column which is time consuming
(multiple
sheets etc)
Alternatively would there be a way of filtering all the columns for
specific words and returning only cells that meet certain criteria

It must be just one of the hang-ups with this damn spreadsheet.
I'll do it the long way instead.
Thanks for the help so far.

"Bernie Deitrick" wrote:

I guess I read it too quickly - after using the go to part, rt-click and choose Delete /
Shift-cells up OR Shift cells left then press OK.

HTH,
Bernie
MS Excel MVP


What if C10 is blank but D10 has data? How are you planning on
deleting just blank cells if the whole row isn't blank?

Damien,

Select columns, then use Edit / Go To... Special Blanks OK, then Rt-Click any of the blank
cells, then choose Delete / Entire Row OK.

HTH,
Bernie
MS Excel MVP

"DamienO" wrote in message

...

I have sheets with data in adjoining columns. Not every cell has data. I want
to removed all the empty cells and leave only a full page of data. At the
moment the only way I can think of is advance filtering for non-blanks but I
have to do this for each individual column which is time consuming (multiple
sheets etc)
Alternatively would there be a way of filtering all the columns for
specific words and returning only cells that meet certain criteria