fan22

07-24-2019, 12:24 PM

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!!

---

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

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!!

---

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