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

[itr674]

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...

 

[pgc01]

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.

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]

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]

Wow! 12 text boxes! I'm glad I was able to help.

Cheers
PGC

 

[itr674]

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...

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]

I tried some modification but the target ranges are giving me the problem...

It is hiding and unhiding over to Hide4.

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]

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]

- 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...