With Statement

[Asi]

Why ?
if I do
Dim rng As Range
Set rng = Application.Union(Worksheets(SheetName).Range(Cells(r, BCol), Cells(r, ACol)), _
Worksheets(SheetName).Range(Cells(r, ZCol), Cells(r, ECol)))
I get an error:
Application defined or object defined error.

BUT when I do:
Dim rng As Range
With Worksheets(SheetName)
Set rng = Application.Union(.Range(.Cells(r, BCol), .Cells(r, ACol)), .Range(.Cells(r, ZCol), .Cells(r, ECol)))
End With
it works fine.

[Artik]

This piece of code: Worksheets(SheetName).Range(Cells(r, BCol), Cells(r, ACol)) you want to refer to cells in SheetName, but with the active worksheet cell (Cells without the dot).
Without With Statement would have to write:Dim rng As Range
Set rng = Application.Union( _
Worksheets(SheetName).Range(Worksheets(SheetName).Cells(r, BCol), Worksheets(SheetName).Cells(r, ACol)), _
Worksheets(SheetName).Range(Worksheets(SheetName).Cells(r, ZCol), Worksheets(SheetName).Cells(r, ECol)))
As you can see, such a code is not readable.

Artik

[Asi]

Finally got the concept.
10x a lot