how to remove blank cells in excel

[Guest]

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!

 

[Guest]

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

 

[JON JON]

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.

 

[Guest]

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!

 

[Gordon]

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

 

[starguy]

press Ctrl+G to open Go To window
click on special tab
check Blanks, press Ok (all blank within your data range will b
selected)
now right click on selection and click delete... and check shift cell
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 b
selected)
now right click on selection and click delete... and check shift cell
up then Ok

hope this would help you

 

[starguy]

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

 

[Guest]

Thank you all for your generous help!