Takes To Long to Run this Macro

[judgejustin]

I need help to see if I have made this more complex than it needs to be. I need it to hide all rows that don't meet a certain criteria, code should be self explanitory. However it takes over a minute to run everytime I open this tab. Any suggestions?

Private Sub Worksheet_Activate()

With Sheets("Void List")

Dim i As Integer

For i = 2502 To 4 Step -1

Rows(i).Hidden = True

If Range("B" & i) = "VOID" Then

Rows(i).Hidden = False

If Range("B" & i) = "" Then

Rows(i).Hidden = True

If Range("B" & i) = "ACTIVE" Then

Rows(i).Hidden = True

If Range("B" & i) = "RESERVE" Then

Rows(i).Hidden = True

End If

End If

End If

End If

Next i

End With

End Sub
[/CODE}

 

[Fluff]

If you have a header row on row 3, how about

Private Sub Worksheet_Activate()

   Me.AutoFilterMode = False
   Me.Range("A3").AutoFilter 2, "Void"
End Sub

 

[judgejustin]

Would this go into my current code somewhere?
If not then I'm not understanding how this would hide the rows that do not have void in them.

 

[Fluff]

It replaces all your code.

 

[judgejustin]

WOW!
I've never used this before. I have had to teach myself how to do macros. This is so awesome, thank you!!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[judgejustin]

Quick question, this gives a drop down sort box in a completely different cell. It is a merged Cell of B1:G1. Is there a way to remove that?
Also, I modified the code so everything was reading from Column one. I placed the header I needed in A2, just in case that made some difference.

Private Sub Worksheet_Activate()

Me.AutoFilterMode = False

Me.Range("A2").AutoFilter 1, "Void"

End Sub

 

[Fluff]

Firstly, merged cells are an abomination & should be avoided like the plague.
If you are getting the filter in row 1, it suggests that you have cells in row 1 merged with cells in row2

 

[judgejustin]

Thank you for all the help

 

[Fluff]

My pleasure.