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

[Scott Steele]

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.

 

[JulieD]

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]

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]

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

 

[Dave R.]

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

 

[Dave Peterson]

Couldn't you just sort the range?

select the range, data|sort

(too manual???)

 

[Scott Steele]

Thanks. That was exactly what I was looking for.