Looping issues

[F&s anaylst]

Hi There,

i have the below code that loop through a list and hides rows that doesn't contain a value of the Textbox, it works fine but becomes very show where number of records it passed through the loop is greater than 1000, is there more efficient way of writing this code?

Number of cells to loop through is 10000 Max

Private Sub tboxSearch_Change() 

LastRow = Cells(rows.count,5).end(xlup).row 

For Each cell In Range(Cells(55, 5), Cells(LastRow,5)) 
If InStr(UCase(cell.Value), UCase(tboxSearch.Value)) = 0 Then 
Rows(cell.Row).EntireRow.Hidden = True 
End If 
Next cell 

End Sub

 

[VoG]

Try

Private Sub tboxSearch_Change()
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, 5).End(xlUp).Row

For Each cell In Range(Cells(55, 5), Cells(LastRow, 5))
    If InStr(UCase(cell.Value), UCase(tboxSearch.Value)) = 0 Then
        Rows(cell.Row).EntireRow.Hidden = True
    End If
Next cell
Application.ScreenUpdating = True
End Sub

 

[F&s anaylst]

Try

Application.ScreenUpdating = False

Application.ScreenUpdating = True

Hi Peter,

i have got that already, the post was only example part of procedure

is it possible to use two-dim arrays so first part of dim will collect the value within the cell and two part will collect the cell row number, then Instr will check the first Dim of collection to see if it contains textbox value and update another variable so it will only contain the row number (second dim) that doesn't contain that value

then i will write the Rows(i).EntrieRow.Hiiden = True to hide the rows that don't contain value that matches the textbox value

I'm looking for a way to do this within looping, so no speed issues and was kindly thinking if arrays is the way to go.

i can do it via coding for autofilter but i thought if process was done within vba engine then it contains better efficiencies and only one line of code to action the hide rows in excel

 

[VoG]

Maybe this

Private Sub tboxSearch_Change()
Dim r As Range, cell As Range, LastRow As Long
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, 5).End(xlUp).Row
For Each cell In Range(Cells(55, 5), Cells(LastRow, 5))
    If InStr(UCase(cell.Value), UCase(tboxSearch.Value)) = 0 Then
        If r Is Nothing Then
            Set r = cell
        Else
            Set r = Union(r, cell)
        End If
    End If
Next cell
If Not r Is Nothing Then r.EntireRow.Hidden = True
Application.ScreenUpdating = True
End Sub

 

[F&s anaylst]

Excellent that speeds up process greatly


thanks again for assistance

 

[Peter_SSs]

Assuming you are not using Autofilter elsewhere on your sheet and row 54 contains a heading, or something that can act as a heading row for AutoFilter, give this a try in a copy of your workbook.



<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> tboxSearch_Change()<br>    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Crit <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> HeaderRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 54    <SPAN style="color:#007F00">'<- Change to suit</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> CheckCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "E"  <SPAN style="color:#007F00">'<- Change to suit</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    ActiveSheet.AutoFilterMode = <SPAN style="color:#00007F">False</SPAN><br>    LastRow = Cells(Rows.Count, CheckCol).End(xlUp).Row<br>    Crit = "*" & tboxSearch.Value & "*"<br>    <SPAN style="color:#00007F">With</SPAN> Cells(HeaderRow, CheckCol).Resize(LastRow - HeaderRow + 1)<br>        <SPAN style="color:#00007F">If</SPAN> Crit = "**" <SPAN style="color:#00007F">Then</SPAN><br>            .AutoFilter Field:=1<br>        <SPAN style="color:#00007F">Else</SPAN><br>            .AutoFilter Field:=1, Criteria1:=Crit<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[F&s anaylst]

Thanks Peter,

Seems Autofilter is the faster way to go

 

[Peter_SSs]

Thanks Peter,

Seems Autofilter is the faster way to go

With 10,000 rows it still seemed pretty quick to me.

 

[Peter_SSs]

Note also that you can use further wildcards in your textbox. For example,
Typing "d*g" into the box would show all rows that contained a "g" somewhere after a "d". So this would show dog, didgeridoo, dating etc
"d?g" would return all rows that contained a "g" exactly two places after a "d". So this would show dog, digger, bulldog etc but not dating or didgeridoo