HIDE Col: based words Hide1/Hide2 in diff cells same row

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
I have several sheets that I need to hide columns in, but the number of columns differs on each sheet.

I can identify the the left most column with Hide1 and the right most column as Hide2. Hide1/Hide2 would always be in row 7 but could be any cell from F7 to M7.

On selection of a cell F10, I would use Private Sub Worksheet_SelectionChange(ByVal Target As Range) to hide the columns from Hide1 to Hide2.

On selection of cell F11, I would unhide the columns from Hide1 to Hide2.

Anyone have any find a word code to set the hide / unhide range...
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi itr674

Please try this code. I used the selection of cells E10 and E11 to trigger the actions instead of F10 and F11, because Hide1 could be in F7 and, in that case, F11 could be hidden.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rH1 As Range, rH2 As Range, bHidden As Boolean

If Target.Address = "$E$10" Or Target.Address = "$E$11" Then
    Set rH1 = Rows(7).Find("Hide1")
    Set rH2 = Rows(7).Find("Hide2")
    bHidden = IIf(Target.Address = "$E$10", True, False)
    If (Not rH1 Is Nothing) And (Not rH2 Is Nothing) And rH1.Column < rH2.Column Then _
        Range(Cells(1, rH1.Column), Cells(1, rH2.Column)).EntireColumn.Hidden = bHidden
End If
End Sub

You can, if you want, add more tests, like if Hide1 and Hide2 are in the columns between F and M.

Hope this helps
PGC

EDIT: Simplified the code
 

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
pgc01--thank you, thank you. I had the columns a little off but your code worked great and I was able to get rid of about 12 test boxes I was using to run the code...
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Wow! 12 text boxes! I'm glad I was able to help.

Cheers
PGC
 

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778

ADVERTISEMENT

pgc01 - can I add two more Hides? Here is the current code, Hide3 and Hide4 wouild be on row 16, same as hide1 and hide2.

The second tarrget address is G12 and G13.

I've tried to do some modificatio but...

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rH1 As Range, rH2 As Range, bHidden As Boolean

If Target.Address = "$F$12" Or Target.Address = "$F$13" Then
    Set rH1 = Rows(16).Find("Hide1")
    Set rH2 = Rows(16).Find("Hide2")
    bHidden = IIf(Target.Address = "$F$12", True, False)
    If (Not rH1 Is Nothing) And (Not rH2 Is Nothing) And rH1.Column < rH2.Column Then _
        Range(Cells(1, rH1.Column), Cells(1, rH2.Column)).EntireColumn.Hidden = bHidden
End If
End Sub
 

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
I tried some modification but the target ranges are giving me the problem...

It is hiding and unhiding over to Hide4.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rH1 As Range, rH2 As Range, rH3 As Range, rH4 As Range, bHidden As Boolean

If Target.Address = "$F$12" Or Target.Address = "$F$13" Or Target.Address = "$g$12" Or Target.Address = "$g$13" Then
    Set rH1 = Rows(16).Find("Hide1")
    Set rH2 = Rows(16).Find("Hide2")
    Set rH3 = Rows(16).Find("Hide3")
    Set rH4 = Rows(16).Find("Hide4")
    bHidden = IIf(Target.Address = "$F$12", True, False)
    If (Not rH1 Is Nothing) And (Not rH2 Is Nothing) And rH1.Column < rH2.Column Then _
        Range(Cells(1, rH1.Column), Cells(1, rH2.Column)).EntireColumn.Hidden = bHidden
    If (Not rH3 Is Nothing) And (Not rH4 Is Nothing) And rH1.Column < rH4.Column Then _
        Range(Cells(1, rH3.Column), Cells(1, rH4.Column)).EntireColumn.Hidden = bHidden
    
End If
End Sub
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi again itr674

I don't understand what you want to do with this last code.

- You now seem to have 2 pairs of controlling cells but you use only one of them, in bHidden. Shouldn't you in this case have 2 bHiddens, one for the first pair of control cells and another for the second pair of control cells?

- You have a typo in the last If, instead of rH1.Column < rH4.Column I think you mean rH3.Column < rH4.Column

Anyway, as I said I don't really understand exacltly what you want the code to do. Can you please explain?

Kind regards
PGC
 

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
- You now seem to have 2 pairs of controlling cells but you use only one of them, in bHidden. Shouldn't you in this case have 2 bHiddens, one for the first pair of control cells and another for the second pair of control cells?PGC

Yes I now have two sets of controlling cells...
 

Forum statistics

Threads
1,141,204
Messages
5,704,952
Members
421,372
Latest member
Jamie11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top