Possible to clean up my formula

Hey people,

Simple formula I have working for me when I run a query on a data sheet. The rows appear when a result is matched in the category.

It is quite repetitive and I find messy. Is it possible to simplify and clean it up ?

Thanks!!

---

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)


    If Range("A9").Value = "" Then
        Rows("5:11").EntireRow.Hidden = True
    ElseIf Range("A9").Value <> "" Then
        Rows("5:11").EntireRow.Hidden = False
    End If
   
    If Range("A17").Value = "" Then
        Rows("13:19").EntireRow.Hidden = True
    ElseIf Range("A17").Value <> "" Then
        Rows("13:19").EntireRow.Hidden = False
    End If
   
    If Range("A25").Value = "" Then
        Rows("21:27").EntireRow.Hidden = True
    ElseIf Range("A25").Value <> "" Then
        Rows("21:27").EntireRow.Hidden = False
    End If
   
    If Range("A33").Value = "" Then
        Rows("29:33").EntireRow.Hidden = True
    ElseIf Range("A33").Value <> "" Then
        Rows("29:33").EntireRow.Hidden = False
    End If
       
    If Range("A39").Value = "" Then
        Rows("35:41").EntireRow.Hidden = True
    ElseIf Range("A39").Value <> "" Then
        Rows("35:41").EntireRow.Hidden = False
    End If
       
    If Range("A47").Value = "" Then
        Rows("43:49").EntireRow.Hidden = True
    ElseIf Range("A47").Value <> "" Then
        Rows("43:49").EntireRow.Hidden = False
    End If


    If Range("A55").Value = "" Then
        Rows("51:57").EntireRow.Hidden = True
    ElseIf Range("A55").Value <> "" Then
        Rows("51:57").EntireRow.Hidden = False
    End If
       
    If Range("A63").Value = "" Then
        Rows("59:65").EntireRow.Hidden = True
    ElseIf Range("A63").Value <> "" Then
        Rows("59:65").EntireRow.Hidden = False
    End If
       
    If Range("A71").Value = "" Then
        Rows("67:73").EntireRow.Hidden = True
    ElseIf Range("A71").Value <> "" Then
        Rows("67:73").EntireRow.Hidden = False
    End If
   
End Sub

---

Not tested

Code:

---

Rows("5:11").Hidden = (Len(Range("A9").Value) = 0)

---

Repeat

Hello fan22,

Here is way to shorten your code...

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
       
    Dim r As Long
   
        If Target.Cells.Count > 1 Then Exit Sub
        If Target.Row <> 1 Then Exit Sub


        Me.UsedRange.Rows.Hidden = False
       
        Application.EnableEvents = False
       
        For r = 9 To 71 Step 8
            If Cells(r, "A") = "" Then
                Range(Rows(r - 4), Rows(r + 2)).Hidden = True
            End If
        Next r
       
      Application.EnableEvents = True


End Sub

---

Shouldn't you be testing that Target, the cell being changed, is one that you want to trigger all of that row hiding, otherwise why do it on a change event at all?

Thanks for your responses, but it doesn't work in my situation. I'll try to explain better what's going on in the workbook.

**I don't have the Power Query function and can't have access to it since administrators blocked our system from downloading additional software.

1) I search the clients ID in "E3", to search all rows in Column A of 9 Sheets where all ID's are identified.

Rows 5:11 is a table representing "Year 1", with the ID cell in "A9", containing the formula below.

=IFERROR(INDEX('Sheet1'!A:A,MATCH($E$3,'Sheet1'!$A:$A,0)),"")

- All other cells with information on this client also feeds into the range with a similar formula

Rows 13:19 with formula for "A17"

=IFERROR(INDEX('Sheet2'!A:A,MATCH($E$3,'Sheet2'!$A:$A,0)),"")

etc...

21:27 - A25

29:33 - A33

35:41 - A39

43:49 - A47

51:57 - A55

59:65 - A63

67:73 - A71

----

I need all these ranges hidden until an ID is identified in the targeted cells which will unhide the range.
- One ID can match multiple "years"

Let me know if you have any questions

Thanks again!

Lots of questions

Didn't really understand your 'example'

Several people answered the question asked, so if possilbe attach a representive workbook with no sensitive data and a detailed explaination of what you'd like

try:

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
With Range("A5:A11,A13:A19,A21:A27,A29:A33,A35:A41,A43:A49,A51:A57,A59:A65,A67:A73")'note these areas are not the same size.
  .EntireRow.Hidden = True
  For Each are In .Areas
    If are.Cells(5).Value <> "" Then are.EntireRow.Hidden = False
  Next are
End With
End Sub

---