VBA To Hide And Unhide Rows

**paul_pearson** · 04-28-2013, 06:09 AM

**Admin** · 04-28-2013, 02:43 PM

Hi Paul,

PFA.

**princ_wns** · 04-28-2013, 04:59 PM

Hi Paul

PFA

Regards
Prince

**paul_pearson** · 04-28-2013, 05:53 PM

Thanks

**bakerman** · 04-30-2013, 06:16 AM

Each row of code less to be read enhances the performance of that code.
So the light versions

Code:

Private Sub ToggleButton1_Click()
    Const Group1 = "D:P"
    Range(Group1).EntireColumn.Hidden = Not Range(Group1).EntireColumn.Hidden
    Me.ToggleButton1.Caption = IIf(Range(Group1).EntireColumn.Hidden, "Group 1 Unhide", "Group 1 Hide")
End Sub

Private Sub ToggleButton2_Click()
    Const Group2 = "Q:AA"
    Range(Group2).EntireColumn.Hidden = Not Range(Group2).EntireColumn.Hidden
    Me.ToggleButton2.Caption = IIf(Range(Group2).EntireColumn.Hidden, "Group 2 Unhide", "Group 2 Hide")
End Sub

Private Sub ToggleButton3_Click()
    Const Group3 = "AB:AI"
    Range(Group3).EntireColumn.Hidden = Not Range(Group3).EntireColumn.Hidden
    Me.ToggleButton3.Caption = IIf(Range(Group3).EntireColumn.Hidden, "Group 3 Unhide", "Group 3 Hide")
End Sub

Code:

Public Sub HideUnhide()
    With ActiveSheet
        Select Case Application.Caller
            Case "btnGrp1":
                .Columns("D:P").Hidden = Not .Columns("D:P").Hidden
            Case "btnGrp2":
                .Columns("Q:AA").Hidden = Not .Columns("Q:AA").Hidden
            Case "btnGrp3":
                .Columns("AB:AI").Hidden = Not .Columns("AB:AI").Hidden
            Case "btnGrpAll":
                .Columns("D:AI").Hidden = Not .Columns("D:AI").Hidden
        End Select
    End With
End Sub

**paul_pearson** · 04-30-2013, 06:41 AM

Hi

I keep getting error - "Run Time Error 13" - Type Mismatch when using this code

Do I need to use a certain type of Button - which is best Button to select
I put the code into a Module

Code:

Public Sub HideUnhide()
    With ActiveSheet
        Select Case Application.Caller
            Case "btnGrp1":
                .Columns("D:P").Hidden = Not .Columns("D:P").Hidden
            Case "btnGrp2":
                .Columns("Q:AA").Hidden = Not .Columns("Q:AA").Hidden
            Case "btnGrp3":
                .Columns("AB:AI").Hidden = Not .Columns("AB:AI").Hidden
            Case "btnGrpAll":
                .Columns("D:AI").Hidden = Not .Columns("D:AI").Hidden
        End Select
    End With
End Sub

Thanks

**bakerman** · 04-30-2013, 01:44 PM

For reference.

**mams323** · 05-07-2013, 01:07 AM

Well done!

Thank you bakerman, you did a fine job.

Could you please use the same file to hide & unhide specific rows instead of colmuns.

Thank you and your efforts are appreciated in advance.

MSABRA

**bakerman** · 05-07-2013, 02:43 PM

Replace this

Code:

.Columns("D:P").Hidden = Not .Columns("D:P").Hidden

by this

Code:

.Rows("10:15").Hidden = Not .Rows("10:15").Hidden

**snb** · 05-07-2013, 03:48 PM

If you rename the button "btnGrpAll" into "btnGrp4" this code suffices:

Code:

Public Sub HideUnhide()
    With ActiveSheet.Columns(Choose(Right(Application.Caller, 1), "D:P", "Q:AA", "AB:AI", "D:AI"))
       .Hidden = Not .Hidden
    End With
End Sub