VBA only keep certain rows visible based on cell value

[Trille]

Hi all,

Been a long time since I last posted and you could say that I'm a sporadic Excel user.. I am facing the following challenge - I have a template sheet that contains 40 identical "matrices", stacked vertically on top of each other in rows of 7 (so starting from row 7, I have a bunch of recurring matrices every 7th line). These matrices represent unique product groups.

This template sheet is automatically copied n times depending on the number of units I have listed in another sheet. However, all product groups are not applicable for all units and I would like the individual sheets to only display the product groups (hence matrices) that apply according to a condition.

The condition is set up according to the following:
- I have another matrix that display product groups on the horizontal axis and units on the vertical axis.
- If a product group is applicable for a particular unit, the user enters an "x" in the appropriate intersection.
- In the template/unit sheet (the copied template sheet) I have entered a simple INDEX/MATCH formula in column A, at the start of each product matrix, to fetch whether this particular group is applicable or not.

Long story short, Is there a way to code in such a way that Excel, starting from row 7, would dynamically display 7 rows for ever "x" it finds in column A and hide the rest? This should also be updated if the user enters new "X:s" in the "condition" matrix. In my simplistic mind, being quite novice at VBA, I am envisioning something like - Starting from row 7, hide everything unless there is an "x" in column A, effectively showing the 7 subsequent rows.

Perhaps this is a stupid way to tackle the problem, but any input would be much appreciated!

Thanks in advance!

Trille

 

[mart37]

Sub hide7rows()
    Dim rng As Range
    Dim lastrow As Long
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    Set rng = Range("A7:A" & lastrow)
    rng.EntireRow.Hidden = True
    For Each c In rng
        If UCase(c.Value) = "X" Then
            Rows(c.Row & ":" & c.Row + 6).EntireRow.Hidden = False
        End If
    Next
End Sub

 

[Trille]

Many thanks for your help, I am so grateful! Did a minor tweak since the code wouldn't allow to look for values in already hidden cells.

Again, thank you Mart!

Sub hide7rows()
Dim rng As Range
Dim lastrow As Long
Rows.EntireRow.Hidden = False
lastrow = Cells(Rows.Count, 2).End(xlUp).Row
Set rng = Range("A7:A" & lastrow)
rng.EntireRow.Hidden = True
For Each c In rng
If UCase(c.Value) = "X" Then
Rows(c.Row & ":" & c.Row + 6).EntireRow.Hidden = False
End If
Next
End Sub