Hide Row on Column and Cell Based evaluation

blewispunk

New Member
Joined
Mar 31, 2003
Messages
12
I have a spreadsheet with a list of questions. Only a portion of these questions will be applicable in any given scenario so I'd like to have a macro to hide the rows of questions that are not applicable. Let's call this sheet("Questions")

I have a separate sheet where someone enters information that is used to calculate what categories of questions are applicable. Let's call this sheet("Applicable")

sheet("Questions").Range("L2:BM2") has all the categories of questions as headers
sheet("Questions").Range("L1:BM1") will calculate as True or False if the category of question is applicable (True) of not applicable (False), based on information in sheet("Applicable")
Sheet("Questions").Range("A4:F500") contains the question text and information. Sheet("Questions").Range("L4:BM500") have a "1" entered if the question is applicable for the category in the heading.

So what I want is a macro that can evaluate each cell in sheet("Questions").Range("L1:BM1") to see if True or False. If True, then evaluate each cell in that column to see if there is a "1" in it, and if so unhide the row. Perhaps there are better ways of doing this using ranges but I'm way out of my league on this one!

Not sure how this will format, but below is an example of what the sheet kind of looks like. In the below example I have applicable categories for Always Ask, Vegetables, and Potatoes. So, I would want it to end up hiding the columns for what smell (Fruit Only which is false) and type of Apple (Apples only which is false)

| |True|True|False|True|False|
|| ||Always Ask||Vegetables Only||Fruit Only||Potatoes Only||Apples Only||
|What color|1 | | | | |
|What Texture| | | | 1| |
|What smell| | |1 | | |
|Type of Apple| | | | 1|

Here is my macro so far but I believe it is pretty far off! Right now I'm getting a type mismatch on my Allrows variable but probably because I'm not using it properly.

Sub Hide_Irrelevant_Questions()
Dim c As Integer
Dim f As Integer
Dim r As Integer
Dim Allrows As Range

r = 4 'First row with questions
f = 500 'Final row with questions

On Error Resume Next
'in case the sheet is protected
ActiveSheet.Cells.EntireRow.Hidden = True

For Each cell1 In Sheets("Diligence Questions").Range("L1:BM1")
If cell1.Value = True Then
cell1.Column = c
For Allrows = r To f
If .Cells(Allrows, c).Value = 1 Then .Rows(Allrows).EntireRow.Hidden = False
Next Allrows
End If

End Sub


ANY HELP GREATLY APPRECIATED!!!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Forum statistics

Threads
1,222,095
Messages
6,163,899
Members
451,865
Latest member
dunworthc

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
Back
Top