CODEWITHSUNDEEP
vbaexcel
lydias
lydias

Reputation: 841

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

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!

Upvotes: 0

Views: 2419

Answers (1)

Tim Williams
Tim Williams

Reputation: 166146

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

Upvotes: 2

Related Questions