How to use Application.Union in the xlwings API way

The Application.Union method in Excel VBA is used to create a single, combined range from two or more individual ranges. This combined range object can then be used for subsequent operations, such as formatting or data manipulation, applied uniformly across all the included cells. In xlwings, this functionality is accessed through the api property of an xlwings object, which provides direct access to the underlying Excel object model. This allows Python scripts to leverage Excel’s powerful range combination logic seamlessly.

Functionality
The primary function of Union is to create a composite Range object. This is particularly useful when you need to perform the same action on multiple, non-contiguous cell blocks without having to loop through each range separately. It streamlines code and improves efficiency.

Syntax in xlwings
The syntax follows the pattern of accessing the VBA method through the xlwings api:

combined_range = xw.apps[0].api.Union(Range1, Range2, ...)

• xw.apps[0].api: This accesses the Application object of the first open Excel instance via xlwings.
• .Union(): The method call.
• Parameters: Range1, Range2, …: These are two or more Range objects that you want to combine. You must provide at least two Range arguments. These ranges can refer to different worksheets or even different workbooks.
• Return Value: The method returns a new Range object representing the union of all specified ranges.

Code Example
The following xlwings script demonstrates the use of Application.Union. It creates a union of three separate ranges on a sheet and then applies a yellow background fill to all cells within the combined range.

import xlwings as xw

# Connect to the active Excel instance and workbook
app = xw.apps.active
wb = app.books.active
sheet = wb.sheets['Sheet1']

# Define three separate, non-adjacent ranges
range1 = sheet.range('A1:B2')
range2 = sheet.range('D4')
range3 = sheet.range('C6:E7')

# Use the Application.Union method via the api property
# Note: We use .api on the sheet's range objects to get the native Excel Range objects for the Union method.
combined_range = app.api.Union(range1.api, range2.api, range3.api)

# Apply formatting to the entire unioned range
combined_range.Interior.Color = (255, 255, 0) # Yellow fill

# The action above fills cells A1, A2, B1, B2, D4, and the block C6:E7.