Excel Hiding Rows with VBA

Question

Ok, so I have hita brick wall, its a big wall for me, but a small wall for you guys I bet, so I'm asking for help.

I have a spreadsheet I use with 110 questions on different rows, which i ask pontential employees and existing employees to answer Y or N in the next column, i then use this to high light various areas of weaknesses or development areas, so i can build them a career and training path. Then, on the 2nd tab, using formulas, the Y's and N's from TAB 1 are used to fill in a chart, with reds and greens, which usually identifies if an employee is technically strong, and needs to work their soft skills or vise versa.
I need your help with TAB 3. Tab 3 will have a list of "suggested readings" and training to undergo to remedy the N's that they may of answered on TAB 1. TAB 1 is called QUESTIONS TAB 2 is called CHART TAB 3 is called FEEDBACK

So, on TAB 3, in CELL B9, my idiot way of thinking says... IF QUESTIONS, C9 = "Y" THEN HIDE FEEDBACK B9.

MarioTheHedgehog · Accepted Answer · 2012-12-17 14:07:44Z

1

So are you asking for the logic or the VBA string?

Cuz your logic is okay, and for VBA it could be something like

Sub HideRows()

For i = 1 to RowCount 'Change RowCount to however many rows there are in Questions & Feedback

If Sheets("Questions").Range("B"& i) = "Y" Then

Sheets("Feedback").Rows(i).Hidden = True

End If

Next i

End Sub

..if I understand you correctly that is

edited Dec 17, 2012 at 14:07

answered Dec 16, 2012 at 14:00

MarioTheHedgehog

3065 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Weston Over a year ago

Thanks for your response! Im really crap at VBA, in fact it scares the hell outta me! Where would add this? Would i open the FEEDBACK tab and then developer, Im using 2010.

NickSlash · Accepted Answer · 2012-12-16 14:50:55Z

You could also use named ranges, if some of your responses show/hide multiple rows it would mean you don't have to hard-code them, you can edit the named ranges.

For my example code to work you would need to write the "name" of the Named Range in the 3'rd column on the same row as the question.

Public Enum HiddenState
Hidden = 0
Shown = 1
Toggle = 2
End Enum

Public Sub Toggle(ByVal Name As String, Optional ByVal State As HiddenState = HiddenState.Toggle)
Dim Row As Variant
For Each Row In Names(Name).RefersToRange.Rows
    If State = Toggle Then
        Row.Hidden = Not Row.Hidden
    Else
        If State = Shown Then Row.Hidden = False Else Row.Hidden = True
    End If
Next
End Sub

Public Sub SetFeedback()
Dim Index As Integer
Dim RowCount As Integer

RowCount = 10 ' Change This

For Index = 1 To RowCount
    If Sheets("Questions").Cells(2, Index) = "Y" Then
        Toggle Sheets("Questions").Cells(3, Index), Hidden
    Else
        Toggle Sheets("Questions").Cells(3, Index), Shown
    End If
Next Index

End Sub

You would need to add this code to a new module, the HiddenState bits aren't needed if your going to explicitly hide and unhide rows.

Collectives™ on Stack Overflow

Excel Hiding Rows with VBA

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related