PDA

View Full Version : Solved: Need Help Hiding Columns



jaydee
04-06-2012, 07:34 PM
Hi,

My worksheet has several names repeated each month and it has a total of 240 columns even though there are less than 2 dozen names.

Is there anyway someone can help me write a code that prompts me for an input box asking for what name I want to see and hide all other columns starting after column J?

Output

http://img18.imageshack.us/img18/3613/outputd.jpg

Bob Phillips
04-07-2012, 02:04 AM
Sub HideAndUnhide()
Dim rng As Range
Dim target As String
Dim lastcol As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

target = InputBox("Select a name to show")
If IsError(Application.Match(target, .Rows(3), 0)) Then

MsgBox "Name not found"
Else

lastcol = .Cells(3, .Columns.Count).End(xlToLeft).Column
.Columns(11).Resize(, lastcol - 10).Hidden = False
For i = 11 To lastcol

If .Cells(3, i).Value <> target Then

If rng Is Nothing Then

Set rng = .Cells(3, i)
Else

Set rng = Union(rng, .Cells(3, i))
End If
End If
Next i
End If

If Not rng Is Nothing Then rng.EntireColumn.Hidden = True
End With

Application.ScreenUpdating = True
End Sub

jaydee
04-07-2012, 10:15 PM
Thanks for your help, works great!