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.

 

[repairman615]

I am having a look...

maybe this would bring the select case around...

<font face=Courier New>                <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Right(Range("A" & Target.Row), 3)</FONT>

 

[hamistasty]

edit: woops, that works great, thanks!!!

 

[hamistasty]

My only problem is that the code takes a long long time to work when ever I select a cell. Is there something I can do to streamline it/speed it up??

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(Range("A" & Target.Row), 3)
        
           Case 1
 
      End Select
    Else
        
End If
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

 

[repairman615]

         Select Case Right(Target, 3)
 
           Case 1
       End Select

This select case is actually doing nothing

If you are not using this, I do not see any need to keep that in there.

 

[hamistasty]

There are 34 select cases in there. All they do is hide a bunch of different columns. There was just no point posting that here as it would take up a lot of space and doesn't affect what I need help with. Sorry!

 

[repairman615]

No worries, I thought that may be they case.

I also think this is what is bogging down the works. Every time a cell is selected, or a change in selection between cells, the code executes.

I think that each of the select case(s) are evaluated every time. I am unsure right now how to take the last 3 numbers and go to what is needed.

In case anyone was wondering about the select cases:

http://www.mrexcel.com/forum/showthread.php?t=557227

I may not be able to come up with a faster way... I will try though.

Jeff

 

[hamistasty]

Thanks Jeff. Display Alerts and Screen Updating are off when it executes so I'm not sure what else to do. I don't think it's the cases it goes through because it didn't have this lag issue until I changed:

Set Changed = Intersect(Target, Range("A6:A" & Range("A" & Rows.Count).End(xlUp).Row))

to

Set Changed = Intersect(Target, Range("A6:DA" & Range("A" & Rows.Count).End(xlUp).Row))

 

[repairman615]

The enable events is worth a shot.

I am really not sure if this will help.

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br> <br><SPAN style="color:#00007F">Dim</SPAN> Changed <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> WS <SPAN style="color:#00007F">As</SPAN> Worksheet, I <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> Wcase <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> Changed = Intersect(Target, Range("A6:DA" & Range("A" & Rows.Count).End(xlUp).Row))<br>Wcase = Right(Range("A" & Target.Row), 3)<br>    <br>    <SPAN style="color:#00007F">With</SPAN> Application<br>        .DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>        .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        .EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>            <SPAN style="color:#00007F">If</SPAN> Target.Cells.Count > 1 <SPAN style="color:#00007F">Then</SPAN> Columns("B:DA").EntireColumn.Hidden = <SPAN style="color:#00007F">False</SPAN><br>                <br>            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Changed <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                  <br>                <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Wcase<br>                <br>                    <SPAN style="color:#00007F">Case</SPAN> 1<br>                        MsgBox 1<br>                    <SPAN style="color:#00007F">Case</SPAN> 2<br>                        MsgBox 2<br>                <br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>                                    <br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <br>    <SPAN style="color:#00007F">With</SPAN> Application<br>        .DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br>        .ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>        .EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>