Combine Data Ranges

[laguna92651]

I have 22 identical data ranges, 5 columns wide, that I want to combine into one range. The ranges are all on the same sheet. I could place the ranges on separate sheets if that make things easier. The data in the ranges is generated by array formulas, so at the end of the range are blank rows, I do not want to include the blank rows in the combined range.
Thanks

 

[SkipVought]

Hi

COPY a range.

PASTE SPECIAL--VALUES empty rows et al

Turn on the auto filter and select the empty rows

Select the filtered empty rows, right click>DELETE

Clear the auto filter

 

[laguna92651]

The blanks actually have formulas in them which I need to keep as the data is updated over time. So if I delete the "blanks" I will lose the formulas. Any other thoughts.

 

[SkipVought]

So you want to retain the formulas?

You may have a problem using the COPY N PASTE method unless your array references are ABSOLUTE.

So I'd DRAG the ranges into the same column group.

Then use the auto filter feature to "hide" the blank rows.

 

[laguna92651]

Can you explain a little more, when you say "drag the ranges into the same column group.

 

[SkipVought]

Select the data in one of your 22 ranges.

Move your cursor to the edge of the selection and observe that the cursor changes to arrows pointing to the 4 points of the compass. Left-Click and move the range at your will.

 

[laguna92651]

Okay thanks. Good solution here, using formulas.
http://www.excelforum.com/excel-gen...grid.com/forum/showthread.php?t=191947[/url}

 

[SkipVought]

There's all kinds of ways to skin a cat.

I avoid formulas that produce "blank rows." There are often better methods, like MS Query.