Possible to clean up my formula

[fan22]

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

[Paul_Hossler]

Not tested




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


Repeat

[Leith Ross]

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

[Bob Phillips]

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?

[fan22]

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!

[Paul_Hossler]

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

[p45cal]

try:
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