Error 1004 with Excel VBA Union

[Jon R]

Hello,

The following is a code extract causing the issue:

  ' WBData is the Excel workbook object and WSData the worksheet object in the workbook
  ' Set the 2 ranges (no issues here)
  Set HdrTblRange = WSData.Range(WSData.Cells(cs_Row_Headers, 1), WSData.Cells(cs_Row_Headers, cs_Col_FCSubmission))
  Set DataTblRange = WSData.Range(WSData.Cells(DRow + cs_Row_Headers, 1), WSData.Cells(DRow + cs_Row_Headers, cs_Col_FCSubmission))
  
  ' Give each range a name (no issues either)
  WBData.Names.Add "FCHdr", HdrTblRange
  WBData.Names.Add "FCData", DataTblRange
  
  ' The following statements result in a "Runtime error 1004_ Method Union of Object _Global failed"
  Set UnionRange = Union(HdrTblRange, DataTblRange)
  Set UnionRange = Union(WSData.Range("FCHdr"), WSData.Range("FCData"))

I can use the ranges without any issues directly or via their names. However the Union statements both result in ""Runtime error 1004_ Method Union of Object _Global failed".

Does anyone have any ideas?

Thanks
Jon

 

[RoryA]

That code runs fine for me in 2016 and 365.

 

[Jon R]

Thanks Rory.

Unfortunately no success. Have built a work around.

 

[RoryA]

Care to share in case it helps anyone else in the future?

 

[Joe4]

Please do not mark a post as the solution if you have not provided the solution!
If you post your actual solution, then you can mark that post as the solution.

 

[Jon R]

Sure - the workaround was:

1) To create a temporary worksheet
2) Copy the first required range from the source to the temp worksheet
3) Copy the second required range from the source to the temp worksheet
4) Set a normal Range (not a Union) on the temp worksheet
5) Process Range as normal

Not pretty but it works!