Selecting cells by value

The example in this section demonstrates how to select cells based on their value. Oddly enough, Excel does not provide a direct way to perform this operation. My selectByValue procedure follows. In this example, the code selects cells that contain a negative value, but this can be changed easily.

Sub SelectByValue()

Dim Cell As Object Dim FoundCells As Range Dim WorkRange As Range

If TypeName(Selection) <> "Range" Then Exit Sub

' Check all or selection?

If Selection.CountLarge = 1 Then

Set WorkRange = ActiveSheet.UsedRange

Else

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

Else

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." Else

FoundCells.Select 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. CD-ROM

This example is available on the companion CD-ROM. The file is named select by value.xlsm .

0 0

Post a comment