CODEWITHSUNDEEP
excelvbashow-hide
John Weston
John Weston

Reputation: 1

Excel Hiding Rows with VBA

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.

Upvotes: 0

Views: 4039

Answers (2)

MarioTheHedgehog
MarioTheHedgehog

Reputation: 306

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

Upvotes: 1

NickSlash
NickSlash

Reputation: 5077

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.

Upvotes: 0

Related Questions