Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
sramsey
 
Posts: n/a
Default Easiest way to delete blank cells in column (not entire row)

Can anyone refresh my memory as to what would be the easiest way to delete
blank cells within a column of data. (Not the entire row - just individual
cells and shift them up to have a list without blanks). I know there are many
ways to do this, some more advanced than others, but I am looking for the
most simple. Thanks in advance!
--
sramsey
  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Easiest way to delete blank cells in column (not entire row)

Select the column
edit|goto|special
check blanks
edit|delete
and choose to shift the cells up.

sramsey wrote:

Can anyone refresh my memory as to what would be the easiest way to delete
blank cells within a column of data. (Not the entire row - just individual
cells and shift them up to have a list without blanks). I know there are many
ways to do this, some more advanced than others, but I am looking for the
most simple. Thanks in advance!
--
sramsey


--

Dave Peterson
  #3   Report Post  
Posted to microsoft.public.excel.misc
sramsey
 
Posts: n/a
Default Easiest way to delete blank cells in column (not entire row)

Thanks Dave - it didn't work for me though - I realized that they aren't
truly blanks, they contain a formula =IF(COUNTIF($A$3:$A$4823,C3)=0,C3,"") it
appears that those are blanks...any new ideas?
--
sramsey


"Dave Peterson" wrote:

Select the column
edit|goto|special
check blanks
edit|delete
and choose to shift the cells up.

sramsey wrote:

Can anyone refresh my memory as to what would be the easiest way to delete
blank cells within a column of data. (Not the entire row - just individual
cells and shift them up to have a list without blanks). I know there are many
ways to do this, some more advanced than others, but I am looking for the
most simple. Thanks in advance!
--
sramsey


--

Dave Peterson

  #4   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Easiest way to delete blank cells in column (not entire row)

Are you sure you want to delete just the cells and shift up?

This sounds like you'll end up with a bunch of inconsistent formulas--going down
that column, that is.

I would cheat a bit.
This formula:
=IF(COUNTIF($A$3:$A$4823,C3)=0,C3,"")
would change to:
=IF(COUNTIF($A$3:$A$4823,C3)=0,C3,na())

Then use the same kind of technique:
Select the column
edit|goto|special
check formulas
but only check errors
edit|delete
and choose to shift the cells up.

Then change those formulas back.



sramsey wrote:

Thanks Dave - it didn't work for me though - I realized that they aren't
truly blanks, they contain a formula =IF(COUNTIF($A$3:$A$4823,C3)=0,C3,"") it
appears that those are blanks...any new ideas?
--
sramsey

"Dave Peterson" wrote:

Select the column
edit|goto|special
check blanks
edit|delete
and choose to shift the cells up.

sramsey wrote:

Can anyone refresh my memory as to what would be the easiest way to delete
blank cells within a column of data. (Not the entire row - just individual
cells and shift them up to have a list without blanks). I know there are many
ways to do this, some more advanced than others, but I am looking for the
most simple. Thanks in advance!
--
sramsey


--

Dave Peterson


--

Dave Peterson
  #5   Report Post  
Posted to microsoft.public.excel.misc
sramsey
 
Posts: n/a
Default Easiest way to delete blank cells in column (not entire row)

Dave - You're aweseome! Thanks so much for the help...that worked perfect!
--
sramsey


"Dave Peterson" wrote:

Are you sure you want to delete just the cells and shift up?

This sounds like you'll end up with a bunch of inconsistent formulas--going down
that column, that is.

I would cheat a bit.
This formula:
=IF(COUNTIF($A$3:$A$4823,C3)=0,C3,"")
would change to:
=IF(COUNTIF($A$3:$A$4823,C3)=0,C3,na())

Then use the same kind of technique:
Select the column
edit|goto|special
check formulas
but only check errors
edit|delete
and choose to shift the cells up.

Then change those formulas back.



sramsey wrote:

Thanks Dave - it didn't work for me though - I realized that they aren't
truly blanks, they contain a formula =IF(COUNTIF($A$3:$A$4823,C3)=0,C3,"") it
appears that those are blanks...any new ideas?
--
sramsey

"Dave Peterson" wrote:

Select the column
edit|goto|special
check blanks
edit|delete
and choose to shift the cells up.

sramsey wrote:

Can anyone refresh my memory as to what would be the easiest way to delete
blank cells within a column of data. (Not the entire row - just individual
cells and shift them up to have a list without blanks). I know there are many
ways to do this, some more advanced than others, but I am looking for the
most simple. Thanks in advance!
--
sramsey

--

Dave Peterson


--

Dave Peterson

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
IF/AND/OR/DATEIF Issue...sorry...long post... EDSTAFF Excel Worksheet Functions 1 November 10th 05 01:28 AM
How to delete blank rows John Mansfield Excel Discussion (Misc queries) 3 April 27th 05 11:48 PM
Blank Cells in Pivot Tables Greg Excel Discussion (Misc queries) 1 March 16th 05 10:23 PM
count non blank cells which meet criteria in another column cmarsh5035 Excel Worksheet Functions 2 February 16th 05 05:32 PM
how can i fill blank cells in column with abc while the right col. khurram saddique Excel Discussion (Misc queries) 2 February 12th 05 04:25 PM


All times are GMT +1. The time now is 07:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"