Macro to hide certain columns & rows (Excel 2003)-VBForums
Results 1 to 11 of 11

Thread: Macro to hide certain columns & rows (Excel 2003)

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Nov 2011
    Posts
    17

    Post Macro to hide certain columns & rows (Excel 2003)

    Hi All,

    Excel 2003

    I was wondering if anyone can help me? I am after a macro (assuming this is the best solution) to hide certain columns and rows based on the value in column B, the macro needs to be triggered using a check box. So when checked the macro hides specific columns & rows and when unchecked shows all

    Example:

    A B D E F
    Test1 Open Test A 1 xx
    Test2 Open Test B 2 yy
    Test3 On Hold Test A 3 zz
    Test4 Closed Test D 4 xx
    Test6 Closed Test E 5 yy

    I have 3 check boxes: Open, Closed and Onhold

    1) When the open box is checked the macro should hide all rows without open in column B and all also hide column D & F
    2) When the Closed box is checked the macro should hide all rows without Closed in column B and all also hide column A & E
    etc....

    To add to the mix is it also possible to filter the results based on 2 columns?

    Example
    1) When the open box is checked the macro should hide all rows without open in column B and showing Test A in column C but hide column D & F

    Hope that makes sense

    Any help will be greatly appreciated

    Thanks in advance

  2. #2

    Thread Starter
    Junior Member
    Join Date
    Nov 2011
    Posts
    17

    Re: Macro to hide certain columns & rows (Excel 2003)

    I have had an attempt at coding this using snippets of google-ing. The issue I having is the rows do not unhide when the check box is unticked, does anyone know where I'm going wrong? Also how would I incorporate the hiding of columns G, I & J into this macro?

    Code:
    Private Sub CheckBox1_Click()
    On Error Resume Next
    If ActiveSheet.CheckBoxes(1).Value = 1 Then
    For Each x In Columns(9).SpecialCells(xlCellTypeConstants)
    If x.Value = "Open" Or x.Value = "Complete" Or x.Value = "On Hold" Then Rows(x.Row).Hidden = True
    Next
    Else
    Rows.Hidden = False
    End If
    End Sub
    Last edited by Siddharth Rout; Apr 8th, 2013 at 02:08 AM. Reason: Added Code Tags

  3. #3
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Macro to hide certain columns & rows (Excel 2003)

    I have 3 check boxes: Open, Closed and Onhold
    If they are checkboxes then why a range comparision? "If x.Value = "Open" Or x.Value = "Complete" Or x.Value = "On Hold" Then Rows(x.Row).Hidden = True" ?

    Also Please do not use "On Error Resume Next" until and unless it is necessary. You might want to see this link?

    Also if you can upload a sample of your file then we would be able to give you a faster resolution If you wish to upload the excel file then zip/rar the file and then upload it.
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  4. #4
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    2,443

    Re: Macro to hide certain columns & rows (Excel 2003)

    When you hide the row(s) you do this:

    Code:
    Rows(x.Row).Hidden = True
    but when you try to unhide you do this:

    Code:
    Rows.Hidden = False
    I would assume you'd need it to be:

    Code:
    Rows(x.Row).Hidden = False

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Nov 2011
    Posts
    17

    Post Re: Macro to hide certain columns & rows (Excel 2003)

    Quote Originally Posted by koolsid View Post
    If they are checkboxes then why a range comparision? "If x.Value = "Open" Or x.Value = "Complete" Or x.Value = "On Hold" Then Rows(x.Row).Hidden = True" ?

    Also Please do not use "On Error Resume Next" until and unless it is necessary. You might want to see this link?

    Also if you can upload a sample of your file then we would be able to give you a faster resolution If you wish to upload the excel file then zip/rar the file and then upload it.

    Hi Koolsid and thanks for taking the time to reply.

    As mentioned I am a novice with macros and have used coding from various threads to get me to this point so apologies if its not logical

    I have upload a sample (with data removed and anonymised due commercial sensitivity) which should give you an idea on what I am trying achieve. I will try to explain.....

    In the very simplest form I would like 2 check boxes to do as follows:

    Checkbox 1:

    When ticked it will only show those rows with "sanctioned" in in column I and also hide columns R -V & AE - CC
    When unticked will show all columns and all rows

    Checkbox 2:
    When ticked it will only show those rows with ("Complete" in column I and "Stage 1" in Column O and also hide columns W -AD, AW - BH & CC) sort by Column M desc
    When unticked will show all columns and all rows

    If I can figure out how to do this I should be able to tailor the code to do what I require for the rest of the checkboxes

    Does this help to clarify? Oh and thanks for the tip on "On Error Resume Next" I now know what i does and agreed it should only be used in certain circumstances!

    Thanks in advance
    Attached Files Attached Files

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Nov 2011
    Posts
    17

    Re: Macro to hide certain columns & rows (Excel 2003)

    Quote Originally Posted by vbfbryce View Post
    When you hide the row(s) you do this:

    Code:
    Rows(x.Row).Hidden = True
    but when you try to unhide you do this:

    Code:
    Rows.Hidden = False
    I would assume you'd need it to be:

    Code:
    Rows(x.Row).Hidden = False
    Thanks for the tip, unfortunately this is not the cause as I tried changing that initially

  7. #7
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Macro to hide certain columns & rows (Excel 2003)

    I saw only one CheckBox in your file so I am taking that example. In case you want to use 3 CB's for different conditions then simply amend the code below.

    Replace your code

    Code:
    Private Sub CheckBox1_Click()
       On Error Resume Next
       If ActiveSheet.CheckBoxes(1).Value = 1 Then
           For Each x In Columns(9).SpecialCells(xlCellTypeConstants)
             If x.Value = "Sanctioned" Or x.Value = "Complete" Or x.Value = "On Hold" Then Rows(x.Row).Hidden = True
            Next
       Else
            Rows.Hidden = False
       End If
    End Sub
    with this

    Code:
    Private Sub CheckBox1_Click()
       If CheckBox1.Value = True Then
            For Each x In Columns(9).SpecialCells(xlCellTypeConstants)
                If x.Value = "Sanctioned" Or _
                   x.Value = "Complete" Or _
                   x.Value = "On Hold" Then Rows(x.Row).Hidden = True
            Next
       Else
            For Each x In Columns(9).SpecialCells(xlCellTypeConstants)
                Rows(x.Row).Hidden = False
            Next
       End If
    End Sub
    Last edited by Siddharth Rout; Apr 8th, 2013 at 04:17 PM.
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Nov 2011
    Posts
    17

    Re: Macro to hide certain columns & rows (Excel 2003)

    Quote Originally Posted by koolsid View Post
    I saw only one CheckBox in your file so I am taking that example. In case you want to use 3 CB's for different conditions then simply amend the code below.

    Replace your code

    Code:
    Private Sub CheckBox1_Click()
       On Error Resume Next
       If ActiveSheet.CheckBoxes(1).Value = 1 Then
           For Each x In Columns(9).SpecialCells(xlCellTypeConstants)
             If x.Value = "Sanctioned" Or x.Value = "Complete" Or x.Value = "On Hold" Then Rows(x.Row).Hidden = True
            Next
       Else
            Rows.Hidden = False
       End If
    End Sub
    with this

    Code:
    Private Sub CheckBox1_Click()
       If CheckBox1.Value = True Then
            For Each x In Columns(9).SpecialCells(xlCellTypeConstants)
                If x.Value = "Sanctioned" Or _
                   x.Value = "Complete" Or _
                   x.Value = "On Hold" Then Rows(x.Row).Hidden = True
            Next
       Else
            For Each x In Columns(9).SpecialCells(xlCellTypeConstants)
                Rows(x.Row).Hidden = False
            Next
       End If
    End Sub

    Excellent that appears to be working perfectly.

    How would I also get the same check box to also hide columns W -AD, AW - BH & CC) and sort the results by Column M desc?

    Many thanks in advance

  9. #9
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Macro to hide certain columns & rows (Excel 2003)

    How would I also get the same check box to also hide columns W -AD, AW - BH & CC) and sort the results by Column M desc?
    Would you like to give it a try first and then post the code that you tried in case you get an error?
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Nov 2011
    Posts
    17

    Re: Macro to hide certain columns & rows (Excel 2003)

    Quote Originally Posted by koolsid View Post
    Would you like to give it a try first and then post the code that you tried in case you get an error?
    Hi Koolsid,

    Sorry for the late reply! And thanks for the getting me on the right track

    Here's my attempt... Seems to work ok but not sure if its the most efficient way of doing it so any advise welcome.

    Private Sub CheckBox1_Click()
    If CheckBox1.Value = True Then
    For Each x In Columns(9).SpecialCells(xlCellTypeConstants)
    If x.Value = "Sanctioned" Or _
    x.Value = "Complete" Or _
    x.Value = "On Hold" Then Rows(x.Row).Hidden = True

    Next
    Range("J:O,R:V,BI:CB").Select
    Selection.EntireColumn.Hidden = True
    ActiveSheet.Range("Q3").Sort Key1:=ActiveSheet.Columns("Q"), Header:=xlGuess
    Else
    For Each x In Columns(9).SpecialCells(xlCellTypeConstants)
    Rows(x.Row).Hidden = False And _
    Range("J:O,R:V,BI:CB").Select
    Selection.EntireColumn.Hidden = False
    Range("B5").Select
    ActiveSheet.Range("B3").Sort Key1:=ActiveSheet.Columns("B"), Header:=xlGuess
    Next
    End If
    End Sub

  11. #11
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    2,443

    Re: Macro to hide certain columns & rows (Excel 2003)

    Couple of suggestions:

    Code:
    Sub hideSort()
        Dim wb As Workbook
        Dim ws As Worksheet
        
        Set wb = Workbooks("myBook.xlsx")   'use your book name
        Set ws = wb.Worksheets("mySheet")   'use this instead of "activesheet," use your sheet name
        
        Range("J:O,R:V,BI:CB").EntireColumn.Hidden = True   'don't need to select the range first
        'ws.Range(b3).Sort...   sort using worksheet object, not activesheet
    End Sub

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.