select case in vba

[Hein]

Hi

I have a select case statement that refers to various cells in different
rows from each other on a worksheet. Based on the answer that the user
provides in these cells a function will be performed whereby a specific row
ranges will be hidden or not.

The problem that I have is that the user does have the option to insert rows
between these cells. This will now have the effect that a code that was
linked to a specific range will now hide a different range that was intended
as the ranges has now shifted downwards.

Is there anyway to overcome this problem?
Hein

 

[Per Jessen]

Hi Hein

You have to use named ranges as reference in your macro.

If you name A1:A10 as Rng1 and
name A11:A20 as Rng2, then user can insert a row, in say 10 and now Rng1
will refer to A1:A11 and Rng2 will refer to A12:A21

In your macro use this:

Range("Rng1").Hide=true

Hopes this helps.
....
Per

 

[Hein]

I have tried the code as follows, but it doesn't work.

Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("B3")) Is Nothing Then Exit Sub
Select Case Target.Value

Case "yes"
Application.ScreenUpdating = False
Range("Rng1").Hide = True
Application.ScreenUpdating = True
Case "no"
Application.ScreenUpdating = False
Range("Rng1").Hide = False
Application.ScreenUpdating = True
End Select

End Sub

I can see on the sheet that the range name has adjsuted if I insert a row
above that. The other problem that I have is that if a row is inserted above
B3(see above where the drop down menu is) then the drop down list moves
downwards from B3 to another cell (C4) and as such the macro won't work. I
presume I need to name the cell and to define it in my macro as well.

Regards

 

[Per Jessen]

Hi

Sure you need to name B3 too.

Suppose you name B3 as DropDownRng then you can use this:

Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("DropDownRng")) Is Nothing Then Exit Sub
Select Case Target.Value

Case "yes"
Application.ScreenUpdating = False
Range("Rng1").Hide = True
Application.ScreenUpdating = True
Case "no"
Application.ScreenUpdating = False
Range("Rng1").Hide = False
Application.ScreenUpdating = True
End Select

End Sub

Regards,
Per

 

[Hein]

Per

Your method worked perfectly. The only problem I picked up was that I had to
change the code
Range("Rng1").Hide = True
to
Range("Rng1").EntireRow.Hidden = True

Thanks very much.
Hein

 

[Per Jessen]

Hein,

Thanks for your reply. I am glad that you made it work, despite my typo.

Regards,
Per