Excel

Select Cells Based on Specific Values

Ease of Use

Easy

Version tested with

2002 

Submitted by:

Jacob Hilderbrand

Description:

A simple method to select a range of cells given certain criteria. 

Discussion:

In our sample, the criteria is the minimum and maximum values that you would like the cells to have. You could use something like this to remove values that fall within a certain range, such as an age, where you only want to keep the ages between 4 and 15. Once selected, you can, for example, choose Edit-Delete and choose "entire row", or you could use additional code to copy the selected cells to another sheet. To run the macro you will need to specify the range to work on, the minimum value, and the maximum value. The macro will then check every cell within your specified range and create a new range whose cells are within the minimum and maximum values specified. In the example the range is A1:B10, Minimum Value is 4, Maximum Value is 15. You can change the range, minimum, and maximum values as needed. 

Code:

instructions for use 

			
Option Explicit 
 
Sub SelectByValue(Rng1 As Range, MinimunValue As Double, MaximumValue As Double) 
     
    Dim MyRange As Range 
    Dim Cell As Object 
     
     'Check every cell in the range for matching criteria.
    For Each Cell In Rng1 
        If Cell.Value >= MinimunValue And Cell.Value <= MaximumValue Then 
            If MyRange Is Nothing Then 
                Set MyRange = Range(Cell.Address) 
            Else 
                Set MyRange = Union(MyRange, Range(Cell.Address)) 
            End If 
        End If 
    Next 
     'Select the new range of only matching criteria
    MyRange.Select 
     
End Sub 
 
Sub CallSelectByValue() 
     
     'Call the macro and pass all the required variables to it.
     'In the line below, change the Range, Minimum Value, and Maximum Value as needed
    Call SelectByValue(Range("A1:B10"), 4, 15) 
     
End Sub

How to use:

  1. Copy the code above.
  2. Open the workbook in which you would like to run this code.
  3. Press Alt + F11 to open the Visual Basic Editor (or VBE).
  4. From the menu, choose Insert-Module.
  5. Paste the code into the code window at right.
  6. Change the Call SelectByValue(Range("A1:B10"), 4, 15) line in the code to match your needs.
  7. Close the VBE.
 

Test the code:

  1. Hit Tools-Macro-Macros and double-click CallSelectByValue.
 

Sample File:

SelectByValue.zip 6.12KB 

Approved by mdmackillop


This entry has been viewed 608 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express