Dynamic Range - When new rows are inserted

[SUR1984]

Hi,
I've been searching to find a solution for my problem for about a week now. Can you please help me on this.

I've a vba code to hide a number of rows based on a selection (Yes or No) in a cell. I've another code to add rows in a table above those rows. Every time a row is added in a table, the range to hide rows has to change. Can it be made dynamic? I'm a novice at vba so please help.

Code to Hide rows is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B13").Value = "No" Then
With Sheets("Main Sheet")
.Rows("32:40").EntireRow.Hidden = True
End With
End If

If Range("B13").Value = "Yes" Then
With Sheets("Main Sheet")
.Rows("32:40").EntireRow.Hidden = False
End With
End If


Need to update the bold reference automatically.

Need to submit my report tomorrow so very anxious for your response.
Thanks in advance
Umair

 

[wideboydixon]

You'll need to keep track of the first row something like this:

Private firstRow As Long
Private Sub Worksheet_Change(ByVal Target As Range)


If firstRow = 0 Then firstRow = 32


With Sheets("Main Sheet")
    .Range(.Cells(firstRow, 1), .Cells(firstRow + 8, 1)).EntireRow.Hidden = (Range("B13").Value = "No")
End With


End Sub

In the code that adds new rows, simply add the number of rows to firstRow when you do so. Also, you'll need to duplicate this line in your other code:

If firstRow = 0 Then firstRow = 32

WBD

 

[SUR1984]

Thanks a lot for the prompt response.
I've tried an alternative solution and it worked. Previously, I wasn't sure if excel updates the name range when a new row is inserted, but it does.
So, I defined the Range 32:40 by a name and used this name in the code.

If Range("B15").Value = "No" Then
With Sheets("Main Sheet")
.Range("Post_Bid_Area").EntireRow.Hidden = True
End With
End If

Thanks again. Will try your code as well.

 

[wideboydixon]

If B15 can only contain either "Yes" or "No" then you don't need to check both conditions. You could use:

Sheets("Main Sheet").Range("Post_Bid_Area").EntireRow.Hidden = (Range("B15").Value = "No")

WBD

 

[SUR1984]

Oh Wow, Instead of 10 lines, it can be done with one. Thanks a lot for all the help and knowledge.