VBA only keep certain rows visible based on cell value

Trille

New Member
Joined
Apr 26, 2010
Messages
25
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
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

New Member
Joined
Apr 26, 2010
Messages
25
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,807
Messages
5,638,478
Members
417,026
Latest member
UDK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top