Looping issues

F&s anaylst

Active Member
Joined
Oct 9, 2008
Messages
277
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

Code:
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
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

Active Member
Joined
Oct 9, 2008
Messages
277
Try

Code:
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Maybe this

Code:
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

Active Member
Joined
Oct 9, 2008
Messages
277

ADVERTISEMENT

Excellent that speeds up process greatly


thanks again for assistance
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Oct 9, 2008
Messages
277

ADVERTISEMENT

Thanks Peter,

Seems Autofilter is the faster way to go
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,394
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top