View Full Version : Possible to clean up my formula

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 ?



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

07-24-2019, 12:39 PM
Not tested

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


Leith Ross
07-24-2019, 12:50 PM
Hello fan22,

Here is way to shorten your 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

07-24-2019, 02:50 PM
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?

07-25-2019, 04:21 AM
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.


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

Rows 13:19 with formula for "A17"



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!

07-25-2019, 05:50 AM
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

07-25-2019, 06:19 AM
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