Hi
I have various cells on a worksheet in different rows that is linked to a
vba case selection, whereby rows are hidden or not based on the drop down
selection in those cells.

The problem that I have is that the user has the option to insert rows in
between these cells which will have the effect that the wrong row ranges will
now be hidden or not as the rows now move downwards depending on how many
rows are inserted.
Is there a way of ensuring that the range selection as per the vba moves
downwards as and when rows are being inserted?
Hein

As ALWAYS, post YOUR code for comments

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Hein" wrote in message
...
Hi
I have various cells on a worksheet in different rows that is linked to a
vba case selection, whereby rows are hidden or not based on the drop down
selection in those cells.

The problem that I have is that the user has the option to insert rows in
between these cells which will have the effect that the wrong row ranges
will
now be hidden or not as the rows now move downwards depending on how many
rows are inserted.
Is there a way of ensuring that the range selection as per the vba moves
downwards as and when rows are being inserted?
Hein

Why did you RE post?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Hein" wrote in message
...
Hi
I have various cells on a worksheet in different rows that is linked to a
vba case selection, whereby rows are hidden or not based on the drop down
selection in those cells.

The problem that I have is that the user has the option to insert rows in
between these cells which will have the effect that the wrong row ranges
will
now be hidden or not as the rows now move downwards depending on how many
rows are inserted.
Is there a way of ensuring that the range selection as per the vba moves
downwards as and when rows are being inserted?
Hein

Hi,
Can you not protect the worksheet with the option of format rows
allowed, this way rows can not be inserted, but rows can be hidden.

JH

"Hein" wrote:

Hi
I have various cells on a worksheet in different rows that is linked to a
vba case selection, whereby rows are hidden or not based on the drop down
selection in those cells.

The problem that I have is that the user has the option to insert rows in
between these cells which will have the effect that the wrong row ranges will
now be hidden or not as the rows now move downwards depending on how many
rows are inserted.
Is there a way of ensuring that the range selection as per the vba moves
downwards as and when rows are being inserted?
Hein

Under the impression the original wasn't posted. However found it now.

The user must have access to insert rows.

The code that is currently there is as follows:
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
Rows("10:20").Select
Selection.EntireRow.Hidden = False
Application.ScreenUpdating = True
Case "no"
Application.ScreenUpdating = False
Rows("10:20").Select
Selection.EntireRow.Hidden = True
Application.ScreenUpdating = True
End Select

End Sub

Effectively it should probably run with a loop whereby the range B3 will
adjust depending on the amount of rows inserted and the rows selections in
the case selection must also adjust depending on the rows inserted.

Thanks

"Don Guillett" wrote:

Why did you RE post?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Hein" wrote in message
...
Hi
I have various cells on a worksheet in different rows that is linked to a
vba case selection, whereby rows are hidden or not based on the drop down
selection in those cells.

The problem that I have is that the user has the option to insert rows in
between these cells which will have the effect that the wrong row ranges
will
now be hidden or not as the rows now move downwards depending on how many
rows are inserted.
Is there a way of ensuring that the range selection as per the vba moves
downwards as and when rows are being inserted?
Hein

.

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Hein" wrote in message
...
Under the impression the original wasn't posted. However found it now.

The user must have access to insert rows.

The code that is currently there is as follows:
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
Rows("10:20").Select
Selection.EntireRow.Hidden = False
Application.ScreenUpdating = True
Case "no"
Application.ScreenUpdating = False
Rows("10:20").Select
Selection.EntireRow.Hidden = True
Application.ScreenUpdating = True
End Select

End Sub

Effectively it should probably run with a loop whereby the range B3 will
adjust depending on the amount of rows inserted and the rows selections in
the case selection must also adjust depending on the rows inserted.

Thanks

"Don Guillett" wrote:

Why did you RE post?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Hein" wrote in message
...
Hi
I have various cells on a worksheet in different rows that is linked to
a
vba case selection, whereby rows are hidden or not based on the drop
down
selection in those cells.

The problem that I have is that the user has the option to insert rows
in
between these cells which will have the effect that the wrong row
ranges
will
now be hidden or not as the rows now move downwards depending on how
many
rows are inserted.
Is there a way of ensuring that the range selection as per the vba
moves
downwards as and when rows are being inserted?
Hein

.