Thanks Norman. Its a pretty long thread. But I took a look at the function
called Inverse(). Effectively it works as follows:
Inverse(Range("N2:BE2"), , Range("N2:BF2")) = $BF$2
Here is a followup question. In one of my subs I have passed a bunch of
range addresses to text string called Addresses1 and Addresses2. They look
as follows:
?Range(formulaAddresses1).Addres
$A$1:$A$2,$C$2:$G$2,$J$2:$N$2,$BI$2,$D$5,$BG$4:$BG$5,$E$6,$D$7:$E$7,$A$4:$A$9,$D$12:$E$12,$D$14,$A$11:$A$15,$A$17,$D$19:$E$19,$A$19:$A$21,$A$23,$A$25,$D$25:$E$25,$D$27:$E$27,$BG$7:$BG$27,$A$27:$A$29,$A$31,$BG$29:$BG$32,$A$33:$A$34,$BG$36,$E$41,$A$36:$A$42
?Range(formulaAddresses2).Addres
$A$1:$A$2,$C$2:$F$2,$H$2,$K$2:$O$2,$BK$2,$A$4:$A$5,$D$5,$BI$4:$BI$5,$E$7,$D$8:$E$8,$A$7:$A$10,$D$13:$E$13,$D$15,$A$12:$A$16,$A$18,$A$20,$D$20:$E$20,$BI$8:$BI$20,$A$22:$A$23,$A$25,$A$27,$D$27:$E$27,$D$29:$E$29,$BI$22:$BI$29,$A$29:$A$31,$A$33,$BI$31:$BI$34
Now I am trying to use the Inverse() function as follows below. I get a
Run-time error '1004'. on the Inverse(). Why is this? Am I breaching a
variable limit? I have included the function from keepItCool below as well
Set Y = Range(Addresses1)
Set Z = Range(Addresses2)
Set LeftOverRange = Inverse(Range(Y), , Range(Z)) '>>>>>'1004' Error
*******************************************
Function Inverse(rngA As Range, Optional bUsedRange As Boolean, _
Optional rngB As Range) As Range
' Freely adapted by keepitcool from :
' Adapted from Norman Jones 2004 Jul 22 'Inverse Selection
' Adapted from thread 2003 Oct 12 'Don't Intersect
' thread contributors Tom Ogilvy, Dave Peterson, Dana DeLouis
Dim lCnt&, itm, colDV As Collection
Dim iEvt%, iScr%
If rngB Is Nothing Then
If bUsedRange Then
Set rngB = rngA.Parent.UsedRange
Else
Set rngB = Square(rngA)
End If
Else
On Error Resume Next
lCnt = Intersect(rngA, rngB).Count
On Error GoTo 0
If lCnt = 0 Then Exit Function Else lCnt = 0
End If
With Application
iEvt = .EnableEvents: .EnableEvents = False
iScr = .ScreenUpdating: .ScreenUpdating = False
End With
Set colDV = New Collection
With Union(rngA, rngB)
useFC:
On Error Resume Next
lCnt = .SpecialCells(xlCellTypeAllFormatConditions).Count
On Error GoTo 0
If lCnt > 0 Then GoTo useDV
.FormatConditions.Add 1, 3, 0
Intersect(rngA, rngB).FormatConditions.Delete
Set Inverse = .SpecialCells(xlCellTypeAllFormatConditions)
Inverse.FormatConditions.Delete
GoTo theExit
useDV:
Do
On Error Resume Next
If IsError(.SpecialCells(xlCellTypeAllValidation)) Then Exit Do
On Error GoTo 0
With Intersect(.Cells, _
.Cells.SpecialCells(xlCellTypeAllValidation) _
.Cells(1).SpecialCells(xlCellTypeSameValidation))
With .Validation
colDV.Add Array(.Parent.Cells, _
.Type, .AlertStyle, .Operator, .Formula1, .Formula2, _
.IgnoreBlank, .InCellDropdown, _
.ShowError, .ErrorTitle, .ErrorMessage, _
.ShowInput, .InputTitle, .InputMessage)
.Delete
End With
End With
Loop
.Validation.Add 0, 1
Intersect(rngA, rngB).Validation.Delete
Set Inverse = .SpecialCells(xlCellTypeAllValidation)
Inverse.Validation.Delete
End With
theExit:
If colDV.Count > 0 Then
For Each itm In colDV
With itm(0).Validation
.Add itm(1), itm(2), itm(3), itm(4), itm(5)
.IgnoreBlank = itm(6)
.InCellDropdown = itm(7)
.ShowError = itm(8)
.ErrorTitle = itm(9)
.ErrorMessage = itm(10)
.ShowInput = itm(11)
.InputTitle = itm(12)
.InputMessage = itm(13)
End With
Next
End If
With Application
.EnableEvents = iEvt
.ScreenUpdating = iScr
Exit Function
End With
End Function
Function Square(rng As Range) As Range
'Finds the 'square outer range' of a (multiarea) range
Dim c1&, cn&, r1&, rn&, x1&, xn&, a As Range
r1 = &H10001: c1 = &H101
For Each a In rng.Areas
x1 = a.Row
xn = x1 + a.Rows.Count
If x1 < r1 Then r1 = x1
If xn > rn Then rn = xn
x1 = a.Column
xn = x1 + a.Columns.Count
If x1 < c1 Then c1 = x1
If xn > cn Then cn = xn
Next
Set Square = rng.Worksheet.Cells(r1, c1).Resize(rn - r1, cn - c1)
End Function
Thanks
EM
