Combining dynamic Ranges

  • Thread starter Thread starter pronto
  • Start date Start date
P

pronto

Hi,

How do I combine two dynamic ranges to become one?

Lets say I have a dynamic range named RANGE_A (column A) and anothe
dynamic range RANGE_B (column B), how do I combine them to be just on
dynamic range, RANGE_C (column C)?

Whatever new data that will be added in RANGE_A or RANGE_B woul
automatically be appended or added to RANGE_C.

Is this possible?

Thanks in advance
 
Hi
not really sure what you're trying to do but try in cell C1:
=A1&B1
and copy this formula down as far as needed
 
From VBA Help keyword "Union"

Sub CombineRanges()
Worksheets("Sheet1").Activate
Set bigRange = Application.Union(Range("Range1"), Range("Range2"))
bigRange.Formula = "=RAND()*1000"
End Sub

HTH
 
Assuming that both ranges have the same header row, you can combine them by
using data, query and use a union query. You need to enter the query by
using the SQL button in the Query program and return the data to the same
workbook. After you set up the query you need to refresh the query after you
enter data in your dynamic ranges. This can be set to be done automatically
 
Assuming that both ranges have the same header row, you can combine them by
using data, query and use a union query. You need to enter the query by
using the SQL button in the Query program and return the data to the same
workbook.
Click to expand...

By 'dynamic range', I think the OP is referring to a Name defined
something like this:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$1:$1)-1,COUNTA(Sheet1!$A:$A)+1)

AFAIK this kind of Name is not exposed by Jet as a table and thus
cannot be queried using sql.

Jamie.

--
 
The dynamic range name is not an issue. If the data on the sheet grows, the
query will grab the data on the sheet. I use this method to combine data
regularly.
Gary
 
Gary Rowe said:
The dynamic range name is not an issue. If the data on the sheet grows, the
query will grab the data on the sheet.
Click to expand...

To do this you need to know the sheet on which the Range returned by
each dynamic Name resides. If you have two dynamic Ranges on the same
sheet, you would additionally need some idea of the cell addresses
involved.

FWIW this is not actually a requirement. The columns don't even have
to be of the same data type e.g.

SELECT MyDateTimeCol, MyTextCol FROM [Sheet1$]
UNION ALL
SELECT MyTextCol, MyDateTimeCol FROM [Sheet1$]

works fine!

Jamie.

--
 
Back
Top