how to remove blank cells in excel

I have an excel table which contains hundreds of rows with variable columns
(e.g. row 1 has data in 5 cells, row 2 has data in 10 cells etc). There are
blank cells in some of the rows and they are not in a regular pattern. Is
there any way I can remove all those blank cells?

Thanks a lot in advance!

Hi Landa,

As far as I know it it only possible to hide/unhide or group columns or rows
in excel,
when you think about it it would basically be impossible to hide seperate
cells, for example, if I have an empty cell in column A, row 5, and I would
hide that cell, cell B5 would have to shift into cell A5, and cause a lot of
problems if you use cell references in formulas. I could be wrong, but I've
never heard of that...

Regards, Nico from the Netherlands

"Landa" wrote:

> I have an excel table which contains hundreds of rows with variable columns
> (e.g. row 1 has data in 5 cells, row 2 has data in 10 cells etc). There are
> blank cells in some of the rows and they are not in a regular pattern. Is
> there any way I can remove all those blank cells?
>
> Thanks a lot in advance!

My suggestion not quite neat but you can give it a try.

Insert a column in A. This will give you blank column at the start of your
sheet
In A1 write this formula =COUNTA(B1:IV1). if your does not start in A1,
then adjust formula accordingly
Drag the formula down up to your last row (Assuming A100). Those rows
with no data will have zero (0) as a result
Select A1 to A100.
hit ctrl+c (copy)
edit>paste special>values
hit ctrl+h (find and replace). Find what: 0, Replace with: (do not type
anything), check match entire cells (click option if you don't see it)
hit ctrl+g (go to), click special, choose blanks, click Ok
edit>delete>entire row

Sorry for my wordings if it is hard to understand.


"Landa" <[email protected]> wrote in message
news:[email protected]...
>I have an excel table which contains hundreds of rows with variable columns
> (e.g. row 1 has data in 5 cells, row 2 has data in 10 cells etc). There
> are
> blank cells in some of the rows and they are not in a regular pattern. Is
> there any way I can remove all those blank cells?
>
> Thanks a lot in advance!

Thanks Nico. Yeah, it's probably impossible to do so, and I am doing it
manually now, which is kinda tedious, so just want to see if there is quicker
way out.
Thanks anyway!

"nicojr" wrote:

> Hi Landa,
>
> As far as I know it it only possible to hide/unhide or group columns or rows
> in excel,
> when you think about it it would basically be impossible to hide seperate
> cells, for example, if I have an empty cell in column A, row 5, and I would
> hide that cell, cell B5 would have to shift into cell A5, and cause a lot of
> problems if you use cell references in formulas. I could be wrong, but I've
> never heard of that...
>
> Regards, Nico from the Netherlands
>
> "Landa" wrote:
>
> > I have an excel table which contains hundreds of rows with variable columns
> > (e.g. row 1 has data in 5 cells, row 2 has data in 10 cells etc). There are
> > blank cells in some of the rows and they are not in a regular pattern. Is
> > there any way I can remove all those blank cells?
> >
> > Thanks a lot in advance!

"Landa" <[email protected]> wrote in message
news:[email protected]...
>I have an excel table which contains hundreds of rows with variable columns
> (e.g. row 1 has data in 5 cells, row 2 has data in 10 cells etc). There
> are
> blank cells in some of the rows and they are not in a regular pattern. Is
> there any way I can remove all those blank cells?
>
> Thanks a lot in advance!


try here:
http://www.cpearson.com/excel/noblanks.htm

press Ctrl+G to open Go To window
click on special tab
check Blanks, press Ok (all blank within your data range will be selected)
now right click on selection and click delete... and check shift cells left then Ok
now click on any cell to remove selection and again
press Ctrl+G to open Go To window
click on special tab
check Blanks, press Ok (all blank within your data range will be selected)
now right click on selection and click delete... and check shift cells up then Ok

hope this would help you

Originally Posted by Landa

Thanks Nico. Yeah, it's probably impossible to do so, and I am doing it
manually now, which is kinda tedious, so just want to see if there is quicker
way out.
Thanks anyway!

"nicojr" wrote:

> Hi Landa,
>
> As far as I know it it only possible to hide/unhide or group columns or rows
> in excel,
> when you think about it it would basically be impossible to hide seperate
> cells, for example, if I have an empty cell in column A, row 5, and I would
> hide that cell, cell B5 would have to shift into cell A5, and cause a lot of
> problems if you use cell references in formulas. I could be wrong, but I've
> never heard of that...
>
> Regards, Nico from the Netherlands
>
> "Landa" wrote:
>
> > I have an excel table which contains hundreds of rows with variable columns
> > (e.g. row 1 has data in 5 cells, row 2 has data in 10 cells etc). There are
> > blank cells in some of the rows and they are not in a regular pattern. Is
> > there any way I can remove all those blank cells?
> >
> > Thanks a lot in advance!

if you want to remove blank cells horizontally then do not repeat the procedure to shift cells up.

Thank you all for your generous help!

"Landa" wrote:

> I have an excel table which contains hundreds of rows with variable columns
> (e.g. row 1 has data in 5 cells, row 2 has data in 10 cells etc). There are
> blank cells in some of the rows and they are not in a regular pattern. Is
> there any way I can remove all those blank cells?
>
> Thanks a lot in advance!

Autofilte can be another way..

Select the column first then,
Data>Filter>Autofilter>Blanks

Select all rows (blindly) and delete them...