How can I dynamically eliminate blank cells in a given range in E.
 

I have several groups of related cells that I have pulled together into a
single range. Now I want to get rid of the blank cells in that range so it
is a single continuous list. There are four groups of five cells each. I
have seen this done in another worksheet, but the formulas for that sheet are
so complicated I don't think I can figure it out.

Hi Scott

here's one suggestion to try on a COPY of your workbook :)

select the range that has the list & blanks in it
choose edit / go to / special and check the blanks option - ok
then choose edit / delete
move cells up or left (as per your data)
Ok

if this gives you what you're after you can do it on the "real thing" :)

cheers
JulieD

"Scott Steele" wrote in message
...
I have several groups of related cells that I have pulled together into a
single range. Now I want to get rid of the blank cells in that range so
it
is a single continuous list. There are four groups of five cells each. I
have seen this done in another worksheet, but the formulas for that sheet
are
so complicated I don't think I can figure it out.

Your proposal is a manual solution. I want the spreadsheet to do the work
for me. I know it can be done, I just can't figure out how. I have a copy
of a spreadsheet that does something similar, but it is pretty complicated.

"JulieD" wrote:

Hi Scott

here's one suggestion to try on a COPY of your workbook :)

select the range that has the list & blanks in it
choose edit / go to / special and check the blanks option - ok
then choose edit / delete
move cells up or left (as per your data)
Ok

if this gives you what you're after you can do it on the "real thing" :)

cheers
JulieD

"Scott Steele" wrote in message
...
I have several groups of related cells that I have pulled together into a
single range. Now I want to get rid of the blank cells in that range so
it
is a single continuous list. There are four groups of five cells each. I
have seen this done in another worksheet, but the formulas for that sheet
are
so complicated I don't think I can figure it out.

Hi Scott

sorry didn't pick up that you wanted a automatic solution ... the only way i
know how to approach this is via the use of code not formulas - is a VBA
solution suitable for you or do you want a formula solution (not that i
think there is one?)

Cheers
JulieD



"Scott Steele" wrote in message
...
Your proposal is a manual solution. I want the spreadsheet to do the work
for me. I know it can be done, I just can't figure out how. I have a
copy
of a spreadsheet that does something similar, but it is pretty
complicated.

"JulieD" wrote:

Hi Scott

here's one suggestion to try on a COPY of your workbook :)

select the range that has the list & blanks in it
choose edit / go to / special and check the blanks option - ok
then choose edit / delete
move cells up or left (as per your data)
Ok

if this gives you what you're after you can do it on the "real thing" :)

cheers
JulieD

"Scott Steele" wrote in message
...
I have several groups of related cells that I have pulled together into
a
single range. Now I want to get rid of the blank cells in that range
so
it
is a single continuous list. There are four groups of five cells each.
I
have seen this done in another worksheet, but the formulas for that
sheet
are
so complicated I don't think I can figure it out.

Formulas can't "get rid" of blank cells - but you can use formulas to
display a list without blanks from a range that includes blanks, if you
don't mind naming some ranges here is a link
http://cpearson.com/excel/noblanks.htm


"Scott Steele" wrote in message
...
I have several groups of related cells that I have pulled together into a
single range. Now I want to get rid of the blank cells in that range so
it
is a single continuous list. There are four groups of five cells each. I
have seen this done in another worksheet, but the formulas for that sheet
are
so complicated I don't think I can figure it out.

Couldn't you just sort the range?

select the range, data|sort

(too manual???)

Scott Steele wrote:

I have several groups of related cells that I have pulled together into a
single range. Now I want to get rid of the blank cells in that range so it
is a single continuous list. There are four groups of five cells each. I
have seen this done in another worksheet, but the formulas for that sheet are
so complicated I don't think I can figure it out.

--

Dave Peterson

Thanks. That was exactly what I was looking for.

"Dave R." wrote:

Formulas can't "get rid" of blank cells - but you can use formulas to
display a list without blanks from a range that includes blanks, if you
don't mind naming some ranges here is a link
http://cpearson.com/excel/noblanks.htm


"Scott Steele" wrote in message
...
I have several groups of related cells that I have pulled together into a
single range. Now I want to get rid of the blank cells in that range so
it
is a single continuous list. There are four groups of five cells each. I
have seen this done in another worksheet, but the formulas for that sheet
are
so complicated I don't think I can figure it out.