Microsoft Office Tutorials and References
In Depth Information
Example 19-12. The SelectIfEmpty Procedure
vPreviousCellValue = vCellValue
End If
Next ' column
Next ' row
' Select the range
If cMatches > 0 Then
rngMatch.Select
cmdUndo.Enabled = False
Else
MsgBox "No matching cells. Selection will not be changed.",
vbInformation
cmdUndo.Enabled = False
End If
End Sub
The SelectIfEmpty and SelectIfNotEmpty procedures are almost identical.
SelectIfEmpty is shown in Example 19-12 .
Example 19-12. The SelectIfEmpty Procedure
Private Sub SelectIfEmpty()
Dim rngMatch As Range
Dim cMatches As Integer
Dim oCell As Object
Dim cRows As Integer, cColumns As Integer
Dim r As Integer, c As Integer
' Get row and column count (one of which is 1)
cColumns = rngSearch.Columns.Count
cRows = rngSearch.Rows.Count
' Start search
cMatches = 0
Set rngMatch = Nothing
For r = 1 To cRows
For c = 1 To cColumns
Set oCell = rngSearch.Cells(r, c)
If IsEmpty(oCell) Then
If rngMatch Is Nothing Then
Set rngMatch = oCell
Else
Set rngMatch = Application.Union(rngMatch, oCell)
End If
cMatches = cMatches + 1
End If
Next ' column
Next ' row
' Select the range
If cMatches > 0 Then
rngMatch.Select
cmdUndo.Enabled = False
Else
MsgBox "No matching cells. Selection will not be changed.",
vbInformation
cmdUndo.Enabled = False
 
Search JabSto ::




Custom Search