Find Question

How do I select the entire row of all items in a column that begin with the letter "SP"
in VBA

Thanks

Mauro

[Tom Urtis]

Assuming your column is A, then try this macro, keeping in mind that the SP is case sensitive, as you presented it in your post.

This does what you ask, which is select the whole row when the value in column A starts with SP.

Thanks to my friend Celia (a former ME regular) for the code syntax inspiration.


Sub SelectSP()
Dim theCol As Range, cell As Range, RtoSel As Range
Dim LtoSel As String
Set theCol = Range(Range("A1"), Range("A65536").End(xlUp))
LtoSel = "SP"
For Each cell In theCol
If Left(cell, 2) = LtoSel Then
If RtoSel Is Nothing Then
Set RtoSel = cell
Else
Set RtoSel = Application.Union(RtoSel, cell)
End If
End If
Next
On Error GoTo e
RtoSel.EntireRow.Select
Exit Sub
e:
MsgBox "There is nothing to select.", vbInformation, "How about that!"
End Sub


HTH

Tom Urtis