Combining Named Ranges

**dmartindale** · 08-10-2007, 03:47 PM

Hi everyone,

I'm looking for a way to take 2 named ranges in a worksheet (that may have space between them) and create a third rectangular named range whose boundaries are the outer-boundaries of the initial 2 ranges. Union will not work because the third range must include any blank space between the initial 2 ranges.

Thanks!

Dave

**Ron Coderre** · 08-10-2007, 04:19 PM

See if this gives you some ideas:

Sub GatherRanges(rRng1 As Range, rRng2 As Range)
   Dim MinCol, MaxCol, MinRow, MaxRow
   Dim rComboRange As Range
   
   With WorksheetFunction
         MinRow = .Min(rRng1.Row, rRng2.Row)
         MinCol = .Min(rRng1.Column, rRng2.Column)
   
         MaxRow = .Max(rRng1.Row + rRng1.Rows.Count, rRng2.Row + rRng2.Rows.Count) - 1
         MaxCol = .Max(rRng1.Column + rRng1.Columns.Count, rRng2.Column + rRng2.Columns.Count) - 1
   End With
   
   Set rComboRange = Range(Cells(MinRow, MinCol), Cells(MaxRow, MaxCol))
   rComboRange.Select
   MsgBox rComboRange.Address
End Sub

Sub Test()
   GatherRanges rRng1:=Range("C3:F5"), rRng2:=Range("H4:L6")
End Sub

Does that help?