Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
select row if find word
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
select row if find word
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Word select from a string | Excel Worksheet Functions | |||
auto word select | Excel Discussion (Misc queries) | |||
Auto Word Select | Excel Discussion (Misc queries) | |||
Auto Word Select | Excel Discussion (Misc queries) | |||
auto word select | Excel Discussion (Misc queries) |