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