Vba macro excel: How to hide rows if cell equal FALSE

Question

I have a project which requires Excel to hide rows on a separate sheet(within the same workbook) after user selects specific options on the activesheet. The macro is linked to a button, when clicked rows will be hidden on the separate sheet, and the whole process occurs in the background. If the user want to check the table with hidden rows they'd need to navigate to that separate sheet to see the result.

Image explanation: http://postimg.org/image/ek6981vg1/

Worksheets("Input- Select Pens") --> active sheet where has the button

Worksheets("Input- Pen") --> separate sheet where has the hidden rows

I have tried several methods, but none of them worked: Method 1:

Sub selectPens()
Dim c As Range
Application.EnableEvents = False
On Error Resume Next
For Each c In Range("E6:E35")
   If c.Value = "FALSE" Then
        Worksheets("Input- Pen").c.EntireRow.Hidden = True
    ElseIf c.Value = "TRUE" Then
        Worksheets("Input- Pen").c.EntireRow.Hidden = False
   End If
 Next c
 On Error GoTo 0
 Application.EnableEvents = True
 End Sub

Method 2:

Sub selectPens()
Dim i As Long
Set wselect = Sheet11
With wselect
For i = 6 To 35
    If ActiveSheet.Cells(i, 5).Value = "FALSE" Then

        .Range("i:i").EntireRow.Hidden = True
        ' .Rows(i).EntireRow.Hidden = True
    ElseIf ActiveSheet.Cells(i, 5).Value = "TRUE" Then
        ' .Rows(i).EntireRow.Hidden = False
        .Range("i:i").EntireRow.Hidden = False
    End If
Next i
End With

End Sub

I would be greatly appreciated for any help.

Many thanks!

Tim Williams · Accepted Answer · 2015-01-08 01:36:03Z

2

Sub selectPens()

    Dim i As Long,  wsselect
    Set wselect = Sheet11

    For i = 6 To 35
        'EDIT
        wselect.Rows(i).Hidden = (ActiveSheet.Cells(i, 5).Value = False)        
    Next i

End Sub

edited Jan 8, 2015 at 1:36

answered Jan 8, 2015 at 1:07

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

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

4 Comments

lydias Over a year ago

Hi Tim, thank you for your prompt response. Sheet11(Input-Pen, on right side of the image) is actually referring to the sheet where I want the rows to be hidden. Sheet10 ("Input- Select Pens") is the activesheet where I place the button that activates the collapsing action.

Thomas Beck Sutton Over a year ago

If that's the case, you can switch out the sheet11 on the answer with sheet10 and then have "Sheets("Sheet10").cells(i,5).entirerow.hidden = true " inside the if statements as needed.

lydias Over a year ago

For some reason I can't get the code to run, no changes are made on sheet11. Could there be some other issues causing the error?

Tim Williams Over a year ago

Edited - try False not "FALSE"

Collectives™ on Stack Overflow

Vba macro excel: How to hide rows if cell equal FALSE

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related