Just reading the subject line I anticipated the problem was going to be not
qualifying Excel's Union function with a reference to the Excel Application,
but I see you have done that.

I can't make out from your code if the arguments in the Union function refer
to valid ranges on the same sheet or if they are returned addresses. I can't
see for the clutter but I suspect not, ie they should be range objects and
not addresses.

Regards,
Peter T


"Mark Burns" wrote in message
...
Excel Gurus: HELP!

I am automating Excel via VBA from Access.
1) I have valid objects for the Applicaiton, and workbook, and i *know*
the
ranges I am working with are NOT EMPTY.
2) the ranges ARE all on the same worksheet.

the problem:
I am building a chart, and the chart has multiple series of data to graph.
The chart has a category X axis.
The lists of Series Categories aren't necessarily all identical, so I need
to build a range which encompases all the unique category values across
all
the series.

My method is to build an initial list of known-unique names (a subset of
the
final list)
and use a little code in nested for each loops to identify all those items
in subsequent ranges which aren't already in the initial range, and UNION
the
cell.Address(External:=True) to the previous range (making a discontiguous
range that points to a complete but also unique list of category values.

The problem I am having is that when I call the UNION method as follows,
the
result is an INVALID range address string!
Ex:
Debug.Print "Pre-UNION oTempDRI.RangeAddress="; oTempDRI.RangeAddress
Debug.Print "n.address="; n.Address(External:=True)

oTempDRI.RangeAddress = _
xlA.Union( _
xlA.Range(oTempDRI.RangeAddress), _
xlA.Range(n.Address(External:=True)) _
).Address(External:=True)

Debug.Print "Post-UNION oTempDRI.RangeAddress="; oTempDRI.RangeAddress

Result in debug window:
Pre-UNION
oTempDRI.RangeAddress=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14
n.address=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$26
Post-UNION oTempDRI.RangeAddress=
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,$B$26

Pre-UNION oTempDRI.RangeAddress=
[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$2:$B$14,$B$26
n.address=[Sheet1]DatarptCorpIncidentsBarsAndLine!$B$28

The 2nd pass through my loop, the .Range method blows up on the
UNION-supplied address string (even with (External:=True) being used?!

Any thoughts/advice?
...or am I just going to have to do some string surgery to manually stuff
the "[...]...!" part of the address back into the resultant string after
the
last "," myself?