CODEWITHSUNDEEP
excelvbaunion
xlmaster
xlmaster

Reputation: 721

having trouble with range union method

Having trouble with vba using range Union method. I have resolved it, at the end. But could not find the proper explanation, why it is so?

Set ws = ThisWorkbook.ActiveSheet
Set rng00 = Range("A1:C3")
Set rng01 = Range("F1:G3")
ws.Union(rng00,rng01).Copy 'the error giving line

When I have changed the line with below one strangely it has started to work. But as I remember I have used union method of range before with ThisWorkbook(or maybe Workbooks(name))

Worksheets("Sheet1").Activate
Application.Union(rng00,rng01).Copy

Upvotes: 1

Views: 1457

Answers (2)

VBasic2008
VBasic2008

Reputation: 54807

Application.Union feat. Qualifying Object References

Your 1st Code

Set ws = ThisWorkbook.ActiveSheet
Set rng00 = Range("A1:C3")
Set rng01 = Range("F1:G3")
ws.Union(rng00,rng01).Copy 'the error giving line

Your 2nd Code

Worksheets("Sheet1").Activate
Application.Union(rng00,rng01).Copy

Application.ThisWorkbook property

  • ThisWorkbook is (a reference to) the workbook containing this code. It is an exact workbook and there can only be one of it.

    Dim wb As Workbook: Set wb = ThisWorkbook

Qualifying Worksheets

  • When referencing the sheets in a workbook, you want to qualify them (note the wb.):

    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")

  • This can be only one worksheet, the worksheet named Sheet1 in the wb - workbook. It can't be the wrong worksheet unless you have incorrectly referenced the workbook.

  • When you do Set ws = ThisWorkbook.ActiveSheet, it creates a reference to any sheet (worksheet, chart) that is currently active (selected, you're looking at), yet in the line Worksheets("Sheet1").Activate you are activating Sheet1˛ of the ActiveWorkbook, the workbook which is the currently active (could be ThisWorkbook, but may not).

Qualifying Ranges

  • When referencing the ranges in a worksheet, you want to qualify them (note the ws.):

    Dim rg1 As Range: Set rg1 = ws.Range("A1:C3")
Dim rg2 As Range: Set rg2 = ws.Range("F1:G3")

  • These are ranges in the ws - worksheet. They can't be the wrong ones unless you have incorrectly referenced the worksheet.

  • When you do Set rng00 = Range("A1:C3"): Set rng01 = Range("F1:G3"), you are creating references to ranges of the ActiveSheet (could be a worksheet, could be a chart), of the ActiveWorkbook (could be ThisWorkbook, but may not). It would have worked correctly only by chance in case your ActiveWorkbook was ThisWorkbook and your ActiveSheet was a worksheet named Sheet1 in the ActiveWorkbook.

Application.Union method

  • Its 'full name' says it all: it's a member of the Application object, not the Worksheet object. You can omit the Application. part from some of the Application object members: Union is such a member.

    Union(rng1, rng2).Copy

Alltogether

Option Explicit

Sub CopyRange()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    Dim rg1 As Range: Set rg1 = ws.Range("A1:C3")
    Dim rg2 As Range: Set rg2 = ws.Range("F1:G3")
    Union(rng1, rng2).Copy
End Sub
  • Of course, if there is no worksheet named Sheet1 in the workbook, an error will occur. But that's another story.

Upvotes: 1

Toddleson
Toddleson

Reputation: 4457

Union is a function of the Application Object, not of a Worksheet Object. Which means that ws.Union would not work because there is no member with the name Union as a member of ws. The correct parent is Application.Union but since it would be tedious to write Application. for every function, most of the time the Application. is not written.

The arguments of Union must be Range Objects that are from the same Worksheet. When declaring Ranges, it is important to explicitly declare their parent sheet, to avoid issues later with functions like Union. Adding ws in front of Range like ws.Range is how you can declare that the range is a member of that sheet.

So the full corrected code would be

Set ws = ThisWorkbook.ActiveSheet
Set rng00 = ws.Range("A1:C3")
Set rng01 = ws.Range("F1:G3")
Application.Union(rng00, rng01).Copy

Upvotes: 0

Related Questions