Macro to hide certain columns & rows

[nkkhil]

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

<tbody>
</tbody>

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

 

[cretsit]

I honestly think Excel's built-in Filter/Table feature would do everything your requested without the need for anything written in VBA. With Filter/Table you can indeed filter results from more than one column.

On the Home tab, look to the far right on the ribbon, and click "Sort & Filter", a drop-down box will appear and click "Filter."

 

[nkkhil]

I honestly think Excel's built-in Filter/Table feature would do everything your requested without the need for anything written in VBA. With Filter/Table you can indeed filter results from more than one column.

On the Home tab, look to the far right on the ribbon, and click "Sort & Filter", a drop-down box will appear and click "Filter."

Thanks for your taking the time to reply.

I am not able to hide columns using this function? I have a spreadheet which contains 70 Coulmns and wish to hide certain rows and certain coulmns based on the values in column B or G. I tried to simplify the example to make it easier

 

[cretsit]

Ah I misunderstood! I thought you meant cells vs. entire columns/rows. Well that's a bit out of my pay grade lol. Best of luck!

 

[nkkhil]

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?

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