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
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
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.
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
Re: Macro to hide certain columns & rows (Excel 2003)
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!
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.
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
Re: Macro to hide certain columns & rows (Excel 2003)
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?
Re: Macro to hide certain columns & rows (Excel 2003)
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