Slow vba code

[Even]

Hi, this code is very slow, and I don't know why. It is a dropdown list in B1. And whenever I choose a name, it takes long time to calculate. Anybody?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer

Application.ScreenUpdating = False
If Not Application.Intersect(Range("B1"), Range(Target.Address)) Is Nothing Then
    Select Case Target.Value
    
    Case Is = "Text":
        For i = 3 To 500
            If Range("AK" & i).Value = "Text" Then
            Rows(i).EntireRow.Hidden = False
            Else: Rows(i).EntireRow.Hidden = True
        End If

    Next i
    
    Case Is = "Text":
        For i = 3 To 500
            If Range("AK" & i).Value = "Text" Then
            Rows(i).EntireRow.Hidden = False
            Else: Rows(i).EntireRow.Hidden = True
        End If
        
        
    
        Application.ScreenUpdating = True

 End Select

    
End Sub

 

[Even]

Okay - I am working on it

By the way - it takes approx. 3.33 sec for each calculation. It is not super duper slow, but still kind of annoying.

 

[Even]

Okay, here is a shot at answering your questions:
- How many values are you really looking for?
* What I am actually finding is names of my colleagues. There are four of us and we divide all our customers among ourselves. As of today, there are around 350 customers, however, the list is growing. The dropdown list in "B1" is to select one of our names, and the result should be that all the customers per the chosen colleague's name is displayed.
- Are these values hard-coded (always the same), or are they coming from somewhere else?
* Yes, the four of us are the constant factor.
- How many rows of data do you really have? One way to limit the number of rows you have to loop through is to dynamically find the last row of data, i.e.
* We have around 350 rows, but that is constantly changing.

The last code I have is just to unhide all rows.

 

[Marc L]

Hi, just use some Excel basics feature like a filter for example in order to get an instant result obviously …​

 

[Even]

I agree with you, Marc. But I don't know how to incorporate that into my model. It consists of a couple of macros that I am afraid will be affected by it. I also have some bad experience having auto filter meaning it filtered some and not others.