the process is taking too long...

[anis]

i've been using this coding n it was working fine until.....i use on a spreadsheet that has 9000++ rows. it's taking too long and also managed to make my pc hang... what can i do about this???

   Range("A2").Offset(1, 0).Select
    Do Until ActiveCell.Value = ""
    If ActiveCell.Text Like "J750*" Or ActiveCell.Text Like "A5*M*" Then ActiveCell.EntireRow.Hidden = True
    If ActiveCell.Text Like "J973*" Or ActiveCell.Text Like "J971*" Then ActiveCell.EntireRow.Hidden = True
    If ActiveCell.Text Like "T33*" Or ActiveCell.Text = "MST-LC" Then ActiveCell.EntireRow.Hidden = True
    If ActiveCell.Text = "MST_LC" Or ActiveCell.Text = "MST-LC" Then ActiveCell.EntireRow.Hidden = True
    If ActiveCell.Text = "C400LPIN" Or ActiveCell.Text = "93K-P603" Then ActiveCell.EntireRow.Hidden = True
    If ActiveCell.Text = "TGRDS-A" Or ActiveCell.Text = "ROOS" Then ActiveCell.EntireRow.Hidden = True
    
    ActiveCell.Offset(1, 0).Select
    Loop

is there any other way without using the above loop statement or advanced filter tools?

thanx in advance
regards,

 

[cherria]

You refer to Activecell a lot.

At the start of the loop, assign it to a variable and then use this variable. You won't be referencing the sheet so often then which should speed things up.

Also, the Like operator is relatively slow, where you are just matching the first few characters (J973*) use instead

= LEFT(variable,5)

and finally, you are using multiple if statements where apparently one would do fine. You can have more than one OR statement in an if statement

Range("A2").Offset(1, 0).Select
Do Until ActiveCell.Value = ""
myvariable=activecell.value
If left(myvariable,4)= "J750" Or myvariable Like "A5*M*" Or left(myvariable,4) = "J973" Or left(myvariable,4)= "J971" Or left(myvariable,3)= "T33" Or myvariable = "MST-LC" Or myvariable= "MST_LC" Or myvariable = "MST-LC" Or ActiveCell.Text = "C400LPIN" Or ActiveCell.Text = "93K-P603" Or ActiveCell.Text = "TGRDS-A" Or ActiveCell.Text = "ROOS" Then ActiveCell.EntireRow.Hidden = True

ActiveCell.Offset(1, 0).Select
Loop

 

[macleanb]

As well as the changes cherria suggests - have you turned off calculation & screenupdating ? This will improve performance (generally).

Application.ScreenUpdating = False/True (dont forget to set it true at the end!)

 

[macleanb]

As well as the changes cherria suggests - have you turned off calculation & screenupdating ? This will improve performance (generally).

Application.ScreenUpdating = False/True (dont forget to set it true at the end!)

 

[HalfAce]

On top of both suggestions above, I don't understand why you're starting with A2, offsetting to A3, and then selecting each cell in Column A throughout your loop.
I would consider rewriting your code to something like this:


<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
x = Cells(65536, 1).End(xlUp).Row
<SPAN style="color:#00007F">For</SPAN> i = 3 <SPAN style="color:#00007F">To</SPAN> x
myvariable = Cells(i, 1).Value
<SPAN style="color:#00007F">If</SPAN> Left(myvariable, 4) = "J750" <SPAN style="color:#00007F">Or</SPAN> myvariable <SPAN style="color:#00007F">Like</SPAN> "A5*M*" _
  <SPAN style="color:#00007F">Or</SPAN> Left(myvariable, 4) = "J973" <SPAN style="color:#00007F">Or</SPAN> Left(myvariable, 4) = "J971" _
  <SPAN style="color:#00007F">Or</SPAN> Left(myvariable, 3) = "T33" <SPAN style="color:#00007F">Or</SPAN> myvariable = "MST-LC" _
  <SPAN style="color:#00007F">Or</SPAN> myvariable = "MST_LC" <SPAN style="color:#00007F">Or</SPAN> myvariable = "MST-LC" _
  <SPAN style="color:#00007F">Or</SPAN> Cells(i, 1).Text = "C400LPIN" <SPAN style="color:#00007F">Or</SPAN> Cells(i, 1).Text = "93K-P603" _
  <SPAN style="color:#00007F">Or</SPAN> Cells(i, 1).Text = "TGRDS-A" <SPAN style="color:#00007F">Or</SPAN> Cells(i, 1).Text = "ROOS" <SPAN style="color:#00007F">Then</SPAN> Cells(i, 1).EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">Next</SPAN> i
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN></FONT>


I think with the screen updating turned off, the use of the variable & single If statement and not actually selecting each cell in the range, your code should run noticeably faster.

Hope it helps,
Dan