select row if find word

[CC]

Hi

I have this code to select the entire row when find a certain word in column
H
sometimes the word is in same column but in several Rows
is it possible select ALL rows where that word is ???


Sub RowsSHOW()
Dim k As Long, myWord As String
myWord = InputBox("Word to find?")
If myWord <> "" Then
For k = Cells(Rows.Count, "H").End(xlUp).Row To 1 Step -1
If InStr(1, CStr(Cells(k, "H")), myWord, vbTextCompare) Then
Rows(k).EntireRow.Select
End If
Next k
End If
End Sub

 

[Dave Peterson]

You can build your range to select after each check.

Option Explicit
Sub RowsSHOW()
Dim k As Long
Dim myWord As String
Dim myRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

myWord = InputBox("Word to find?")

If myWord <> "" Then
With wks
For k = .Cells(.Rows.Count, "H").End(xlUp).Row To 1 Step -1
If InStr(1, CStr(.Cells(k, "H")), myWord, vbTextCompare) Then
If myRng Is Nothing Then
Set myRng = .Cells(k, "A")
Else
Set myRng = Union(myRng, .Cells(k, "A"))
End If
End If
Next k
End With
End If

If myRng Is Nothing Then
MsgBox "Not found"
Else
'wks.Select 'not needed if you're processing the activesheet
myRng.EntireRow.Select
End If

End Sub

=================
Instead of looping through the cells, you may want to look at VBA's sample for
..FindNext.

Depending on the size of your data, you may find it works a lot faster.

 

[ccruz]

Select Row If Find A Word

Great

Is it possible associate the ROW1:1 as a header ?

ccruz

You can build your range to select after each check.

Option Explicit
Sub RowsSHOW()
Dim k As Long
Dim myWord As String
Dim myRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

myWord = InputBox("Word to find?")

If myWord <> "" Then
With wks
For k = .Cells(.Rows.Count, "H").End(xlUp).Row To 1 Step -1
If InStr(1, CStr(.Cells(k, "H")), myWord, vbTextCompare) Then
If myRng Is Nothing Then
Set myRng = .Cells(k, "A")
Else
Set myRng = Union(myRng, .Cells(k, "A"))
End If
End If
Next k
End With
End If

If myRng Is Nothing Then
MsgBox "Not found"
Else
'wks.Select 'not needed if you're processing the activesheet
myRng.EntireRow.Select
End If

End Sub

=================
Instead of looping through the cells, you may want to look at VBA's sample for
..FindNext.

Depending on the size of your data, you may find it works a lot faster.



CC wrote:
>
> Hi
>
> I have this code to select the entire row when find a certain word in column
> H
> sometimes the word is in same column but in several Rows
> is it possible select ALL rows where that word is ???
>
> Sub RowsSHOW()
> Dim k As Long, myWord As String
> myWord = InputBox("Word to find?")
> If myWord <> "" Then
> For k = Cells(Rows.Count, "H").End(xlUp).Row To 1 Step -1
> If InStr(1, CStr(Cells(k, "H")), myWord, vbTextCompare) Then
> Rows(k).EntireRow.Select
> End If
> Next k
> End If
> End Sub

--

Dave Peterson