Sorting Cells

[DamienO]

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

 

[Bernie Deitrick]

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

 

[Spiky]

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

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?

 

[Bernie Deitrick]

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

 

[DamienO]

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

 

[Spiky]

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

 

[DamienO]

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.

 

[Bernie Deitrick]

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]

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.