I am using Excel 2007 but the file is saved as 97-2003. A2:K2 are merged, and
when I manually hide columns in this range it works fine but when I run it
through the code it hides column A through K.

"Dave Peterson" wrote:

First, nothing to do with your question...

Me.Rows("6:17").Select
Selection.EntireRow.Hidden = False
Me.Columns("D:AB").Select
Selection.EntireColumn.Hidden = False

could be replaced with:

Me.Rows("6:17").EntireRow.Hidden = False
Me.Columns("D:AB").EntireColumn.Hidden = False

(And the same with the other .select/selection.entirecolumn... stuff, too.)

There's not too many things that code needs to select first before working with
them.

Second, This is a guess...

I'm guessing that you have merged cells in your worksheet.

And you may find that hiding a cell that is merged with cells in columns that
are hidden will hide all the columns in that merged area (this behavior changes
with versions of excel, though).

What version of excel has the problem for you.



Ayo wrote:

I have this Worksheet event that I am trying to use to hide rows and columns
based on the value in Range("A2"). The code looks OK and it doesn't give any
error except that it doesn't quite does want it is intended to do.
For instance, when it is run, columns A:L are hidden everytime. This is not
the intent for the code and I can't figure out why it is doing this. Any
helpful insight will be greatly appreciated.
The entire code follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False

Me.Rows("6:17").Select
Selection.EntireRow.Hidden = False
Me.Columns("D:AB").Select
Selection.EntireColumn.Hidden = False

If Target.Address() = "$A$2" Then
Application.EnableEvents = False
Select Case Target.Value

Case "NLP1 Infill: Milestone Completion Counts by Market"
Me.Range("E:F,I:K,N:N,P:Q,U:W,AA:AA").Select
Selection.EntireColumn.Hidden = True
Me.Range("7:7,12:12,15:15").Select
Selection.EntireRow.Hidden = True

Case "NLP2: Milestone Completion Counts by Market"
Me.Range("E:K,N:N,W:W,Y:AA").Select
Selection.EntireColumn.Hidden = True
Me.Range("9:9,11:11,13:13").Select
Selection.EntireRow.Hidden = True

Case "NLP3: Milestone Completion Counts by Market"
Me.Range("E:K,M:M,U:Z").Select
Selection.EntireColumn.Hidden = True
Me.Range("13:13").Select
Selection.EntireRow.Hidden = True
End Select
End If

Application.EnableEvents = True
End Sub

--

Dave Peterson