Hiding columns with code

[keithb]

Is there a better way to wright this code? I have trouble hidding one of the columns somtimes. The names in the code are actually in row 2 in there column range. I would rather have an "IF" selection (IF $A$1 = "$R$2) etc...hide columns, but I did not know how to do that.
Any help would be appreciated.
keithb

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set A1 = Range("A1")
If Intersect(t, A1) Is Nothing Then Exit Sub
Application.EnableEvents = False
ActiveSheet.Unprotect "jonna1"
If Target.Address = "$A$1" And Target.Text = "Keith Blankenship" Then
Range("R:R").EntireColumn.Hidden = False
Else
Range("R:V").EntireColumn.Hidden = True
If Target.Address = "$A$1" And Target.Text = "Rick Silverman" Then
Range("S:S").EntireColumn.Hidden = False
Else
Range("R:V").EntireColumn.Hidden = True
If Target.Address = "$A$1" And Target.Text = "Richard Sawyer" Then
Range("T:T").EntireColumn.Hidden = False
Else
Range("R:V").EntireColumn.Hidden = True
If Target.Address = "$A$1" And Target.Text = "Ron Naylor" Then
Range("U:U").EntireColumn.Hidden = False
Else
Range("R:V").EntireColumn.Hidden = True
If Target.Address = "$A$1" And Target.Text = "George Rodriguez" Then
Range("V:V").EntireColumn.Hidden = False
Else
Range("R:V").EntireColumn.Hidden = True
End If
End If
End If
End If
End If
Call Macro3
ActiveSheet.Protect "jonna1"
Application.EnableEvents = True
End Sub
Sub Macro3()
'
' Macro3 Macro
' Filter
'
ActiveSheet.Range("$A$2:$W$54").AutoFilter Field:=23, Criteria1:="<>"
End Sub

 

[Krishnakumar]

Hi,

May be..

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [a1]) Is Nothing Then Exit Sub
Dim strTarget   As String

Me.Unprotect "jonna1"
Range("r:v").EntireColumn.Hidden = 1

strTarget = LCase$(Target.Text)

Select Case strTarget
    Case LCase$(Cells(2, "r"))
        Range("r:r").EntireColumn.Hidden = 0
    Case LCase$(Cells(2, "s"))
        Range("s:s").EntireColumn.Hidden = 0
    Case LCase$(Cells(2, "t"))
        Range("t:t").EntireColumn.Hidden = 0
    Case LCase$(Cells(2, "u"))
        Range("u:u").EntireColumn.Hidden = 0
    Case LCase$(Cells(2, "v"))
        Range("v:v").EntireColumn.Hidden = 0
End Select
Me.Protect "jonna1"
End Sub

 

[Peter_SSs]

Perhaps it might be this?

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> NameFound <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range("A1")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Columns("R:V").Hidden = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> NameFound = Range("R2:V2").Find(What:=Range("A1").Value, _<br>            LookAt:=xlWhole, MatchCase:=False, SearchFormat:=<SPAN style="color:#00007F">False</SPAN>)<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> NameFound <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            NameFound.EntireColumn.Hidden = False<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[keithb]

Thank you Krishnakumar and Peter SSs for your response.
Both methods work but my macro also hides all the blank rows in column "W". I use a dropdown box in "A1" with about 25 names, only 4 names selected will unhide the column they are in. Other than that I like how much cleaner this is. I don't know how to do this without a macro. I know very little about code as you can tell. Any help is greatly appreciated.

Keithb

 

[Peter_SSs]

.. but my macro also hides all the blank rows in column "W".

You can easily just put your code that does that back in the suggested code (just as you probably did with the Unprotect/Protect that I also didn't include in my code)

I use a dropdown box in "A1" with about 25 names, only 4 names selected will unhide the column they are in.

Are you saying this is what you want to happen or what the suggeted code does and that is not what you want to happen?

If there are 25 available names in the A1 drop-down and only 5 columns from R:V what is supposed to happen when a name is chosen in A1 that does not occur in R2:V2?

I don't know how to do this without a macro.

Unless you want to manually hide/unhide columns and rows you can't do it without a macro.

 

[keithb]

Re: Hiding columns with code/Solved

Thanks for response Petter SSs.
The data that is placed into this spreadsheet will have various formulas that will perform calculations on this data based on the "IF" formula.
These calculations will be performed when the "A1" matches the formula and the result is placed into column "W", therefore only the results will show for the persons name that matches the cell "A1". In other words there may be 30 rows of data but any name in cell "A1" may only be located in 2 or 3 rows of that data. As a result of the calculations performed, a sum would appear in column "W" and all other blank rows of data would be hidden.
Any way Peter SSs I placed my code into your code and it works like a charm. Thanks a million
Keithb