Good catch! I completely forgot that Validations can lie outside of the
UsedRange. Okay, to solve this problem, I created an UnusedRange function to
get around fact that SpecialCells(xlCellTypeBlanks) won't look outside of
the UsedRange. Below is my coded solution. The OP should copy all of this
into a Module (Insert/Module from the VB editor's menu bar) and run the
DeleteUnusedValidations macro.
Sub DeleteUnusedValidations()
On Error Resume Next
Worksheets("Sheet3").UsedRange.SpecialCells(xlCellTypeAllValidation). _
SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 18
UnusedRange("Sheet3").SpecialCells(xlCellTypeAllValidation). _
Interior.ColorIndex = 18
End Sub
Function UnusedRange(WorksheetName As String) As Range
Dim UR As Range
Dim WS As Worksheet
Set WS = Worksheets(WorksheetName)
Set UR = WS.UsedRange
With UR
With .Offset(.Rows.Count, .Columns.Count)
Set UnusedRange = .Resize(1, WS.Columns.Count - _
.Column + 1).EntireColumn
Set UnusedRange = Union(UnusedRange, .Resize(WS.Rows.Count - _
.Row + 1, 1).EntireRow)
End With
If UR.Row > 1 Then
Set UnusedRange = Union(UnusedRange, WS.Range("A1", _
WS.Range(Split(.Offset(-1).Address, _
":")(0))).EntireRow)
End If
If UR.Column > 1 Then
Set UnusedRange = Union(UnusedRange, WS.Range("A1", _
WS.Range(Split(.Offset(, -1).Address, _
":")(0))).EntireColumn)
End If
End With
End Function
--
Rick (MVP - Excel)
Oops. I didn't catch the part about validated cells being blank. I
thought that was too easy. <g>
But on this topic, Rick, what if the OP had blank validated cells
outside the UsedRange, which is in an area xlCellTypeBlanks doesn't
seem to evaluate but xlCellTypeValidation does. Without both working
outside the UsedRange, it's no good to catch those cells. The user
could do a xlCellTypeValidation selection and then loop through the
selection finding blank cells. That makes for a smaller loop, but is
there a loopless way? Is there a way to force xlCellTypeBlanks to
evaluate all selected cells, not just the UsedRange?
You know... in case I'm asked this on a game show. <lol>
-Melina