+ Reply to Thread
Results 1 to 5 of 5

Hide and Show Colums using VBA

Hybrid View

  1. 04-18-2016, 01:45 AM #1
    Aisha890
    Aisha890 is offline
    Registered User
    Join Date
    04-09-2016
    Location
    London,England
    MS-Off Ver
    2010
    Posts
    10

    Hide and Show Colums using VBA

    Hi

    Can you tell me a VBA Code where I can Hide and Show using a button different columns within a sheet. So as the user presses Hide the columns will be hidden and vice versa for the show.

    Regards
    Reply With QuoteRegister To Reply
  2. 04-18-2016, 02:01 AM #2
    Trebor76
    Trebor76 is offline
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,527

    Re: Hide and Show Colums using VBA

    Hi Aisha890:

    Have a look at these:

    Option Explicit
Sub Macro1()
    
    Columns("C:D").EntireColumn.Hidden = True 'Hides columns C and D
        
End Sub
Sub Macro2()

    Columns("C:D").EntireColumn.Hidden = False 'Unhides columns C and D

End Sub
    Regards,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post
    Reply With QuoteRegister To Reply
  3. 04-19-2016, 03:48 AM #3
    Aisha890
    Aisha890 is offline
    Registered User
    Join Date
    04-09-2016
    Location
    London,England
    MS-Off Ver
    2010
    Posts
    10

    Re: Hide and Show Colums using VBA

    I need a code where in columns K and M wherever there is date. Upon clicking the hide button it will hide and show for the vice versa.

    Also when I try to connect it with the button by altering the code from the view code option. It takes me to the code instead of performing the actions. Please tell me how to resolve it.
    Last edited by Aisha890; 04-19-2016 at 04:31 AM.
    Reply With QuoteRegister To Reply
  4. 04-19-2016, 03:47 AM #4
    Aisha890
    Aisha890 is offline
    Registered User
    Join Date
    04-09-2016
    Location
    London,England
    MS-Off Ver
    2010
    Posts
    10

    Re: Hide and Show Colums using VBA 

    Sub Hide()

       Columns("K:M").EntireColumn.Hidden = True 'Hides columns K and M
        Columns("Q:S").EntireColumn.Hidden = True 'Hides columns Q and S
        Columns("W:Y").EntireColumn.Hidden = True 'Hides columns W and Y
     Columns("AC:AE").EntireColumn.Hidden = True 'Hides columns AC and AE
     Columns("AI:AK").EntireColumn.Hidden = True 'Hides columns AI and AK
     Columns("AO:AQ").EntireColumn.Hidden = True 'Hides columns AO and AQ
     Columns("AU:AW").EntireColumn.Hidden = True 'Hides columns AU and AW
     Columns("BA:BC").EntireColumn.Hidden = True 'Hides columns BA and BC
     Columns("BG:BI").EntireColumn.Hidden = True 'Hides columns BG and BI
     Columns("BM:BO").EntireColumn.Hidden = True 'Hides columns BM and BO
     Columns("BS:BU").EntireColumn.Hidden = True 'Hides columns BS and BU
     Columns("BY:CA").EntireColumn.Hidden = True 'Hides columns BY and CA
     Columns("CE:CG").EntireColumn.Hidden = True 'Hides columns CE and CG
     Columns("CK:CM").EntireColumn.Hidden = True 'Hides columns CK and CM
     Columns("CQ:CS").EntireColumn.Hidden = True 'Hides columns CQ and CS
     Columns("CW:CY").EntireColumn.Hidden = True 'Hides columns CW and CY
     Columns("DC:DE").EntireColumn.Hidden = True 'Hides columns DC and DE
     Columns("DI:DK").EntireColumn.Hidden = True 'Hides columns DI and DK
     Columns("DO:DQ").EntireColumn.Hidden = True 'Hides columns DO and DQ
     Columns("DU:DW").EntireColumn.Hidden = True 'Hides columns DU and DW
     Columns("EA:EC").EntireColumn.Hidden = True 'Hides columns EA and EC
     Columns("EG:EI").EntireColumn.Hidden = True 'Hides columns EG and EI
     Columns("EM:EO").EntireColumn.Hidden = True 'Hides columns EM and EO
     Columns("ES:EU").EntireColumn.Hidden = True 'Hides columns ES and EU
     Columns("EY:FA").EntireColumn.Hidden = True 'Hides columns EY and FA
     Columns("FE:FG").EntireColumn.Hidden = True 'Hides columns FE and FG
      
End Sub

    and for show 

     Sub Show()

     Columns("K:M").EntireColumn.Hidden = False 'Unhides columns K and M
     Columns("Q:S").EntireColumn.Hidden = False 'Unhides columns Q and S
     Columns("W:Y").EntireColumn.Hidden = False 'Unhides columns W and Y
     Columns("AC:AE").EntireColumn.Hidden = False 'Unhides columns AC and AE
     Columns("AI:AK").EntireColumn.Hidden = False 'Unhides columns AI and AK
     Columns("AO:AQ").EntireColumn.Hidden = False 'Unhides columns AO and AQ
     Columns("AU:AW").EntireColumn.Hidden = False 'Unhides columns AU and AW
     Columns("BA:BC").EntireColumn.Hidden = False 'Unhides columns BA and BC
     Columns("BG:BI").EntireColumn.Hidden = False 'Unhides columns BG and BI
     Columns("BM:BO").EntireColumn.Hidden = False 'Unhides columns BM and BO
     Columns("BS:BU").EntireColumn.Hidden = False 'Unhides columns BS and BU
     Columns("BY:CA").EntireColumn.Hidden = False 'Unhides columns BY and CA
     Columns("CE:CG").EntireColumn.Hidden = False 'Unhides columns CE and CG
     Columns("CK:CM").EntireColumn.Hidden = False 'Unhides columns CK and CM
     Columns("CQ:CS").EntireColumn.Hidden = False 'Unhides columns CQ and CS
     Columns("CW:CY").EntireColumn.Hidden = False 'Unhides columns CW and CY
     Columns("DC:DE").EntireColumn.Hidden = False 'Unhides columns DC and DE
     Columns("DI:DK").EntireColumn.Hidden = False 'Unhides columns DI and DK
     Columns("DO:DQ").EntireColumn.Hidden = False 'Unhides columns DO and DQ
     Columns("DU:DW").EntireColumn.Hidden = False 'Unhides columns DU and DW
     Columns("EA:EC").EntireColumn.Hidden = False 'Unhides columns EA and EC
     Columns("EG:EI").EntireColumn.Hidden = False 'Unhides columns EG and EI
     Columns("EM:EO").EntireColumn.Hidden = False 'Unhides columns EM and EO
     Columns("ES:EU").EntireColumn.Hidden = False 'Unhides columns ES and EU
     Columns("EY:FA").EntireColumn.Hidden = False 'Unhides columns EY and FA
     Columns("FE:FG").EntireColumn.Hidden = True 'Unhides columns FE and FG
     


 End Sub


    However it shows the code as in break mode
    Reply With QuoteRegister To Reply
  5. 04-19-2016, 07:28 AM #5
    Trebor76
    Trebor76 is offline
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,527

    Re: Hide and Show Colums using VBA

    This code will hide your desired columns if they're not hidden or show them if they're hidden:

    Option Explicit
Sub Macro1()
    
    Dim varMyCol As Variant
    
    Application.ScreenUpdating = False
    
    'When hiding or unhiding it's best to work backwards through the columns.
    For Each varMyCol In Split("FE:FG,EY:FA,ES:EU,EM:EO,EG:EI,EA:EC,DU:DW,DO:DQ,DI:DK,DC:DE,CW:CY,CQ:CS,CK:CM,CE:CG,BY:CA,BS:BU,BM:BO,BG:BI,BA:BC,AU:AW,AO:AQ,AI:AK,AC:WE,W:Y,Q:S,K:M", ",")
        If Columns(varMyCol).EntireColumn.Hidden = True Then
            Columns(varMyCol).EntireColumn.Hidden = False
        Else
            Columns(varMyCol).EntireColumn.Hidden = True
        End If
    Next varMyCol
    
    Application.ScreenUpdating = True
    
End Sub
    If the code is going into break mode I'm guessing there's a syntax error with it. I've tested mine and there's no issue.

    Regards,

    Robert
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Hide/Show Colums via drop down list on a different sheet
    By realrookie in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-24-2015, 05:52 AM
  2. Macro to hide colums
    By mohk123 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-24-2013, 01:04 AM
  3. [SOLVED] Hide non ajacent colums with a Macro
    By Shazz in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-01-2013, 09:54 AM
  4. freeze first colums and hide one colums
    By pejoi in forum Excel General
    Replies: 2
    Last Post: 08-09-2012, 03:50 AM
  5. Hide show colums showing list of works
    By excelwanabe in forum Excel General
    Replies: 9
    Last Post: 02-21-2012, 09:24 AM
  6. Macro to Hide Colums
    By keen learner in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-08-2006, 08:10 AM
  7. How can I hide data in colums.
    By Gord in forum Excel General
    Replies: 4
    Last Post: 06-01-2005, 04:05 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1