I Tried to write a function which returns a compilation of ranges by using Union in a loop. Then function in principle looks like this:
Public Function rRange() As Range
Dim rSubRange As Range
Set rRange = .... any range
For i = 1 To 5
Set rSubRange = .... any range build with i
Set rRange = Application.Union(rRange, rSubRange)
rRange.Select ' This is just to monitor how the range "grows"
Next i ' I do stop the execution here to check the selection
The problem is that rRange allways just comprises of the very last rSubRange. I.e. the Union does not grow as expected.
Welcome to the forum.
This works fine for me:
Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.Sub x() rRange End Sub Public Function rRange() As Range Dim rSubRange As Range Dim i As Long Set rRange = Range("A1") For i = 1 To 5 Set rSubRange = rRange.Offset(i) Set rRange = Application.Union(rRange, rSubRange) rRange.Select Next i End Function
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks