Microsoft Office Tutorials and References
In Depth Information
Working with Ranges
If Selection.CountLarge = 1 Then
Set WorkRange = ActiveSheet.UsedRange
Set WorkRange = Application.Intersect(Selection, ActiveSheet.UsedRange)
End If
‘ Reduce the search to numeric cells only
On Error Resume Next
Set WorkRange = WorkRange.SpecialCells(xlConstants, xlNumbers)
If WorkRange Is Nothing Then Exit Sub
On Error GoTo 0
‘ Loop through each cell, add to the FoundCells range if it qualifies
For Each Cell In WorkRange
If Cell.Value < 0 Then
If FoundCells Is Nothing Then
Set FoundCells = Cell
Set FoundCells = Union(FoundCells, Cell)
End If
End If
Next Cell
‘ Show message, or select the cells
If FoundCells Is Nothing Then
MsgBox “No cells qualify.”
End If
End Sub
The procedure starts by checking the selection. If it’s a single cell, then the entire worksheet is
searched. If the selection is at least two cells, then only the selected range is searched. The range
to be searched is further refined by using the SpecialCells method to create a Range object
that consists only of the numeric constants.
The code within the For-Next loop examines the cell’s value. If it meets the criterion (less than
0), then the cell is added to the FoundCells Range object by using the Union method. Note
that you can’t use the Union method for the first cell. If the FoundCells range contains no
cells, attempting to use the Union method will generate an error. Therefore, the code checks
whether FoundCells is Nothing .
When the loop ends, the FoundCells object will consist of the cells that meet the criterion (or
will be Nothing if no cells were found). If no cells are found, a message box appears. Otherwise,
the cells are selected.
This example is available on the companion CD-ROM. The file is named select by
value.xlsm .
Search JabSto ::

Custom Search