Unhide Columns When Range is Selected

[hamistasty]

Using

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

I want to make it so that the merged cell range of H1:CH3 (which would be H1 right?) unhides columns A:AAA when it is selected/active. So far I have:

If Target.Cells.Count > 1 Then Exit Sub
       If Not Intersect(Target, Range("H1")) Is Nothing Then         
            Columns("A:AAA").EntireColumn.Hidden = False
                Else  
                End If

Not sure what I'm doing wrong.

 

[Boller]

"I want to make it so that the merged cell range of H1:CH3 (which would be H1 right?)"

Wrong. Check what this produces :-

MsgBox Target.Address

Perhaps :-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target(1).Address = "$H$1" Then Columns("A:AAA").EntireColumn.Hidden = False
End Sub

 

[hamistasty]

That doesn't work. The problem might be that this column reset is being conducted whilst there are hidden columns including sometimes H column. But even when it's not, and H column is present, it does not work. I'll provide the rest of the code in case that helps.

Option Explicit
    
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
Dim Changed As Range
Dim WS As Worksheet, I As Long
Set Changed = Intersect(Target, Range("A6:A" & Range("A" & Rows.Count).End(xlUp).Row))
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Changed Is Nothing Then
         Select Case Right(Target, 3)
           Case 1
       End Select
       
  
     
     ElseIf Target(1).Address = "$H$1" Then Columns("A:AAA").EntireColumn.Hidden = False
       
        Else
     
        
    End If
     Application.DisplayAlerts = True
        Application.ScreenUpdating = True
 
End Sub

 

[hamistasty]

I did have

        Else
       Columns("A:AAA").EntireColumn.Hidden = False

Which would work on every cell except the ones that are merged. So I think the problem is because H1 is a bunch of merged cells (H1 to HC3). What's with that?

 

[hamistasty]

By the way the msg box produces nothing when I click on a merged cell. So I'm 100% certain that is the problem. How do you make a merged cell group do something when selected???

 

[hamistasty]

dURR The problem was this line: If Target.Cells.Count > 1 Then Exit Sub

So for future reference if anyone is after the same thing, this was the final code:

Option Explicit
    
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
Dim Changed As Range
Dim WS As Worksheet, I As Long
Set Changed = Intersect(Target, Range("A6:A" & Range("A" & Rows.Count).End(xlUp).Row))
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    If Not Changed Is Nothing Then
         Select Case Right(Target, 3)
           Case 1
   
       End Select
       
ElseIf ActiveCell.Range("H1") = True Then
    MsgBox "not merged"
Else
    Columns("A:DA").EntireColumn.Hidden = False
    
End If
     Application.DisplayAlerts = True
        Application.ScreenUpdating = True

End Sub

 

[Sektor]

If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("H1:CH3")) Is Nothing Then         
        Columns("A:A").EntireColumn.Hidden = False
    Else
        Columns("A:A").EntireColumn.Hidden = True
    End If
End If

 

[repairman615]

That line of target.cells.count >1 then exit sub is a line I use to make things happen to only one cell. I guess the merged is an exception to that.



Glad you got this sorted!

Jeff

Ps. Boller.. target(1) ... very cool!

 

[hamistasty]

Thanks for the help. I didn't use that exact code so I basically took the If cell greater out of the main code and made it it's own If statement like you did, except I made it so every merged cell clicked (or if multiple cells are highlighted) then all the columns are unhidden.

However, the code runs really slow when the main code happens. Basically all I changed since it was running fast is I made the range it checks A6:DA instead of A6:A (the one column after row 6, instead of every column and row from A6 onwards.

How could I streamline this code so it runs faster??

Option Explicit
    
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
Dim Changed As Range
Dim WS As Worksheet, I As Long
Set Changed = Intersect(Target, Range("A6:DA" & Range("A" & Rows.Count).End(xlUp).Row))
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    
If Target.Cells.Count > 1 Then
   
        Columns("B:DA").EntireColumn.Hidden = False
    
 ElseIf Not Changed Is Nothing Then
         Select Case Right(Target, 3)
        
           Case 1
       End Select
    Else
End If
     Application.DisplayAlerts = True
        Application.ScreenUpdating = True
End Sub

 

[hamistasty]

Blah just realised that doesn't even work. It's checking the cell I click on to see what number is in it. How do I change the above to check the Row I've clicked on for the Value in Column A to carry out a case??
Right now it's carrying out the case in the cell I check within the Range of "Changed".