Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: VBA To Hide And Unhide Rows

  1. #1
    Senior Member
    Join Date
    Mar 2013
    Posts
    107
    Rep Power
    12

    VBA To Hide And Unhide Rows

    Hi

    Columns D to P (group 1)
    Columns Q to AA (group 2)
    Columns AB to AI (group 3)
    Is there a VBA/macro I can use where it hides these Columns Groups .Each group is assigned to a Button so I can hide/unhide…example:each button would be labelled according to there Group number and diplays either hide or unhide depending the state
    I can hide/unhide any 1 group, both groups or all groups at any one time
    I will like the buttons to be situated in Column A and in order going down the column

    Any help appreciated



    Paul


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
    https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
    https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
    https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Attached Files Attached Files
    Last edited by DocAElstein; 10-24-2023 at 02:57 PM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi Paul,

    PFA.
    Attached Files Attached Files
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Member
    Join Date
    Nov 2011
    Posts
    41
    Rep Power
    0
    Hi Paul

    PFA

    Regards
    Prince
    Attached Files Attached Files

  4. #4
    Senior Member
    Join Date
    Mar 2013
    Posts
    107
    Rep Power
    12
    Thanks

  5. #5
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    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

  6. #6
    Senior Member
    Join Date
    Mar 2013
    Posts
    107
    Rep Power
    12
    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
    Last edited by Admin; 04-30-2013 at 01:53 PM.

  7. #7
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    For reference.
    Attached Files Attached Files

  8. #8
    Junior Member
    Join Date
    May 2013
    Posts
    11
    Rep Power
    0
    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

  9. #9
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    Replace this
    Code:
    .Columns("D:P").Hidden = Not .Columns("D:P").Hidden
    by this
    Code:
    .Rows("10:15").Hidden = Not .Rows("10:15").Hidden

  10. #10
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    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

Similar Threads

  1. Hide Ribbon, Gridlines, Heading and FormulaBar using VBA
    By LalitPandey87 in forum Excel Help
    Replies: 7
    Last Post: 06-10-2013, 04:41 PM
  2. How to condense several rows into fewer rows?
    By gavin_machine in forum Excel Help
    Replies: 2
    Last Post: 10-03-2011, 11:15 PM
  3. Hide/Unhide Columns with Listbox
    By obed_cruz in forum Excel Help
    Replies: 2
    Last Post: 05-28-2011, 07:26 PM
  4. Hide and Unhide Rows and Columns
    By Admin in forum Download Center
    Replies: 0
    Last Post: 05-11-2011, 12:00 AM
  5. Replies: 2
    Last Post: 05-06-2011, 02:59 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •