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!

how to remove blank cells in excel
 

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!

how to remove blank cells in excel
 

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)
editpaste specialvalues
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
editdeleteentire row

Sorry for my wordings if it is hard to understand.


"Landa" wrote in message
...
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!

how to remove blank cells in excel
 

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!

how to remove blank cells in excel
 

"Landa" wrote in message
...
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 he
http://www.cpearson.com/excel/noblanks.htm

how to remove blank cells in excel
 

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

Landa Wrote:
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!


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=567794

how to remove blank cells in excel
 

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


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=567794

how to remove blank cells in excel
 

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!