Hiding Rows and Columns in code

[Ayo]

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]

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]

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]

And if you unmerge those cells, does your code work ok?

If it does, I think you have two choices.

Live with the merged cells and the code not working the way you want
or
remove the merged cells permanently and have the code work the way you want.

 

[Ayo]

Thanks Dave. I figured out a thrid alternative; copy the sheet, Delete
shift:=Left all the cells that I needed to delete and everything works great
now. Just the way I wanted it to.
Thanks for all the insight, really appreciated.

 

[Dave Peterson]

Glad you found something that worked for you.

Thanks Dave. I figured out a thrid alternative; copy the sheet, Delete
shift:=Left all the cells that I needed to delete and everything works great
now. Just the way I wanted it to.
Thanks for all the insight, really appreciated.