excel 2007 macro select records

Question

I would like to write a macro in vb to select all rows where values in column B contain the characters 'via'. This is my first attempt at macros and not too sure how to begin.

Answer 1

This one should do it for you (it worked for me) - code adapted from here.

Option Explicit

Sub SelectByValue(Rng1 As Range, Value As String)

    Dim MyRange As Range
    Dim Cell As Object

     'Check every cell in the range for matching criteria.
    For Each Cell In Rng1
        If InStr(1, Cell.Text, "via") 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 and the Value as needed
    Call SelectByValue(Range("B1:B10"), "via")

End Sub

How to use it?

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("B1:B10"),"via") line in the code to match your needs.
7. Close the VBE.

How to test the code?

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

Before running the Macro:

After running the Macro: