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
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
Re: Macro to hide certain columns & rows (Excel 2003)
Quote:
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.
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
1 Attachment(s)
Re: Macro to hide certain columns & rows (Excel 2003)
Quote:
Originally Posted by
koolsid
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
Re: Macro to hide certain columns & rows (Excel 2003)
Quote:
Originally Posted by
vbfbryce
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
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
Re: Macro to hide certain columns & rows (Excel 2003)
Quote:
Originally Posted by
koolsid
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
Re: Macro to hide certain columns & rows (Excel 2003)
Quote:
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? ;)
Re: Macro to hide certain columns & rows (Excel 2003)
Quote:
Originally Posted by
koolsid
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
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