Name Range Outside Range

[mcpop7]

I have a range in a worksheet named 'myrange' which refers to a block of
cells

I want to name the range that is outside of this, i.e all cells outside
of 'myrange'

Any suggestions would be warmly received!

 

[Hamilton R. Romano]

Try this,

Sub OutsideRange()
Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range, rng5 As Range
Application.Calculation = xlCalculationManual
r = Range("myrange").Row
rs = Range("myrange").Rows.Count
c = Range("myrange").Column
cs = Range("myrange").Columns.Count
If c > 1 Then
Set rng1 = Range(Cells(1, 1), Cells(65536, c - 1))
Set rng5 = rng1
End If
If r > 1 Then
Set rng2 = Range(Cells(1, c), Cells(r - 1, c + cs - 1))
If rng5 Is Nothing Then
Set rng5 = rng2
Else: Set rng5 = Application.Union(rng2, rng5)
End If
End If
If r + rs < 65537 Then
Set rng3 = Range(Cells(r + rs, c), Cells(65536, c + cs - 1))
If rng5 Is Nothing Then
Set rng5 = rng3
Else: Set rng5 = Application.Union(rng3, rng5)
End If
End If
If c + cs < 257 Then
Set rng4 = Range(Cells(1, c + cs), Cells(65536, 256))
Set rng5 = Application.Union(rng4, rng5)
End If
rng5.Name = "outsideRange"
End Sub

Regards,
Hamilton R. Romano