speed up this macro?

[cmefly]

hi,

i've got a simple macro that is hiding rows based on a condition....but it takes almost an entire minute to run......any ideas on how to speed this up?

Private Sub Worksheet_Activate()
Dim x As Variant
Dim y As String
Dim i As Integer
Dim j As Integer
Dim b As String

Dim col As Integer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual



col = 1

Rows("23:2806").Select
Selection.EntireRow.Hidden = False
Range("A24").Select

For i = 24 To 1200

y = Range("a" & i).Value

x = Application.VLookup(y, Sheets("hidden1").Range("a1:b22"), col, 0)

If IsError(x) Then
Rows(i).EntireRow.Hidden = True
End If

b = Range("O" & i).Value

If IsEmpty("O" & i) Then
Rows(i).EntireRow.Hidden = False
End If

If b = "Hide" Then
Rows(i).EntireRow.Hidden = True
End If
Next i

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
end sub



thank you in advance.

cmefly

 

[wigi]

What version of Excel are you running?

PS: Please post code on the Board within

 tags. Thanks.

 

[cmefly]

excel 2003

i can't use the code tags...system security...

 

[cmefly]

any help would be greatly appreciated.

 

[wigi]

To me, I can be wrong, it seems that this whole bunch of code and loop should have just been an Advanced filter case.

 

[wigi]

any help would be greatly appreciated.

It's rather pathetic to already ask for help 5 minutes after your previous post...

 

[cmefly]

I meant to put both messages together wigi..

thanks for you help...i'll try to implement it.

 

[wigi]

I can advice the advanced filter, since it allows to filter and bring up records that match the criteria. The criteria could be listed as you have now: cells a1:b22 on sheet hidden1. The other conditions should also be taken up in the criteria range, I think it should be possible.