Macro hiding all cells

[Peter Lyle]

Hi Team,
Can you please assist with hiding out why the macro is hiding all cell when I'm selecting a Entity from a drop down list

Macro:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Entities As Range
Set Entities = Range("D2")

If Intersect(Target, Entities) Is Nothing Then Exit Sub

Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim Rng4 As Range
Dim Rng5 As Range
Dim Rng6 As Range
Dim Rng7 As Range
Dim Rng8 As Range

Dim FindHdg1 As Range
Dim FindHdg2 As Range
Dim FindHdg3 As Range
Dim FindHdg4 As Range
Dim FindHdg5 As Range
Dim FindHdg6 As Range
Dim FindHdg7 As Range
Dim FindHdg8 As Range

Set FindHdg1 = Cells.Find("Individuals")
Set FindHdg2 = Cells.Find("Company")
Set FindHdg3 = Cells.Find("Joint")
Set FindHdg4 = Cells.Find("Associations")
Set FindHdg5 = Cells.Find("Government Body")
Set FindHdg6 = Cells.Find("Partnership")
Set FindHdg7 = Cells.Find("SMSF")
Set FindHdg8 = Cells.Find("Trust")

Dim RowsToHide As Range
Set RowsToHide = Range("A17:A100")


Select Case Entities
Case Is = "All"
Cells.EntireRow.Hidden = False

Case Is = "Individuals"
Cells.EntireRow.Hidden = False
Set Rng1 = FindHdg1.CurrentRegion
RowsToHide.EntireRow.Hidden = True
Rng1.EntireRow.Hidden = False

Case Is = "Company"
Cells.EntireRow.Hidden = False
Set Rng2 = FindHdg2.CurrentRegion
RowsToHide.EntireRow.Hidden = True
Rng2.EntireRow.Hidden = False

Case Is = "Joint"
Cells.EntireRow.Hidden = False
Set Rng3 = FindHdg3.CurrentRegion
RowsToHide.EntireRow.Hidden = True
Rng3.EntireRow.Hidden = False

Case Is = "Associations"
Cells.EntireRow.Hidden = False
Set Rng4 = FindHdg4.CurrentRegion
RowsToHide.EntireRow.Hidden = True
Rng4.EntireRow.Hidden = False

Case Is = "Government Body"
Cells.EntireRow.Hidden = False
Set Rng5 = FindHdg5.CurrentRegion
RowsToHide.EntireRow.Hidden = True
Rng5.EntireRow.Hidden = False

E.g.,

Case Is = "Partnership"
Cells.EntireRow.Hidden = False
Set Rng6 = FindHdg6.CurrentRegion
RowsToHide.EntireRow.Hidden = True
Rng6.EntireRow.Hidden = False

Case Is = "SMSF"
Cells.EntireRow.Hidden = False
Set Rng7 = FindHdg7.CurrentRegion
RowsToHide.EntireRow.Hidden = True
Rng7.EntireRow.Hidden = False

Case Is = "Trust"
Cells.EntireRow.Hidden = False
Set Rng8 = FindHdg8.CurrentRegion
RowsToHide.EntireRow.Hidden = True
Rng8.EntireRow.Hidden = False
End Select

End Sub

 

[DanteAmor]

Hi @Peter Lyle.
Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Set FindHdg1 = Cells.Find("Individuals")

You should search only in column A. Understanding that the entities are in column A. If you search in all cells, the first cell it finds with the entity is cell D2. So it should be like this:

Set FindHdg1 = Range("A:A").Find("Individuals")

The code can be simplified like this:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range
 
  If Intersect(Target, Range("D2")) Is Nothing Then Exit Sub
 
  Range("A17:A100").EntireRow.Hidden = False
  Set rng = Range("A:A").Find(Target.Value, , xlValues, xlWhole, , , False)
  If Not rng Is Nothing Then
    Range("A17:A100").EntireRow.Hidden = True
    rng.CurrentRegion.EntireRow.Hidden = False
  End If
End Sub

You should consider that the CurrentRegion property takes the continuous cells until it finds a blank row.
Considering the above, you will have as a result something like this:
E.g.1:

E.g.2:

--------------

Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[Peter Lyle]

@DanteAmor
Thank you for your assistance with this issue.
I have tried the code and it works a treat.
I may need further assistance, If the Entities e.g., SMSF and then the Regulation is selected to show only through options and so on for the Sub-Entities and Addition-Entities, but Regulation is in column B and Sub-Entities is in column C and Addition-Entities is D

 

[Peter_SSs]

@DanteAmor
Thank you for your assistance with this issue.
I have tried the code and it works a treat.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.