Macro for hiding rows not working

Hi,

I’ve a macro to hide certain rows if I select a value on cell J6 from a dropdown data validation list. Everything works perfect until I select the option “ALL” from the list. Once I select “ALL” on J6 the workbook stops responding and then I have to use task manager to end the process.

Can someone please help me fix this issue with the vba code. Have attached a sample workbook along with the vba code.

Regards,
SWSample Data.xls

Hey there,
Try updating you entire coding behind the Sector Dashboard to just look like this. I believe the rest might be unneccessary.

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)

If Range("J6").Value <> "ALL" Then
    Filter_By_Sector
Else
    Cells.EntireRow.AutoFit
End If

End Sub



Sub Filter_By_Sector()
Dim r As Long 'rows to check
Dim Hiders As Range, Found As Range
Dim Cond As String
'
'The Name you want displayed is from cell K6
Cond = [J6].Value
'
Application.ScreenUpdating = False
Call Show_All 'Unhide previous names if any
For r = 19 To ActiveSheet.UsedRange.Rows.Count
Set Found = Range("C" & r, "L" & r).Find(What:=Cond, _
After:=Range("D" & r), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Found Is Nothing Then
If Hiders Is Nothing Then
Set Hiders = Rows(r)
Else
Set Hiders = Union(Hiders, Rows(r))
End If: End If: Next r
Hiders.EntireRow.Hidden = True
Application.ScreenUpdating = True
End Sub

That was quick. Thanks so much for all your help!!!!

Thanks for the star tap!