Vba to hide/unhide rows depending on cell value

[neilcsmith1984]

Hi,

I have created the below VBA to hide rows depending on the value in A99, which works fine.
However, when the value in cell A99 changes to a higher value it hides the required rows, but does not unhide the rows above.

Is there any way to adjust the formula, to unhide the previous rows

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("Overview")


Thanks

If Target.Address = "$A$99" Then
If Target.Value = 1 Then Rows("102:119").EntireRow.Hidden = True
If Target.Value = 2 Then Rows("103:119").EntireRow.Hidden = True
If Target.Value = 3 Then Rows("104:119").EntireRow.Hidden = True
If Target.Value = 4 Then Rows("105:119").EntireRow.Hidden = True
If Target.Value = 5 Then Rows("106:119").EntireRow.Hidden = True
If Target.Value = 6 Then Rows("107:119").EntireRow.Hidden = True
If Target.Value = 7 Then Rows("108:119").EntireRow.Hidden = True
If Target.Value = 8 Then Rows("109:119").EntireRow.Hidden = True
If Target.Value = 9 Then Rows("110:119").EntireRow.Hidden = True
If Target.Value = 10 Then Rows("111:119").EntireRow.Hidden = True
If Target.Value = 11 Then Rows("112:119").EntireRow.Hidden = True
If Target.Value = 12 Then Rows("113:119").EntireRow.Hidden = True
If Target.Value = 13 Then Rows("114:119").EntireRow.Hidden = True
If Target.Value = 14 Then Rows("115:119").EntireRow.Hidden = True
If Target.Value = 15 Then Rows("116:119").EntireRow.Hidden = True
If Target.Value = 16 Then Rows("117:119").EntireRow.Hidden = True
If Target.Value = 17 Then Rows("118:119").EntireRow.Hidden = True
If Target.Value = 18 Then Rows("119:119").EntireRow.Hidden = True
If Target.Value > 18 Then Rows("102:119").EntireRow.Hidden = False
End If
End With
End Sub

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$A$99" Then
      Rows("102:119").Hidden = False
      If Target.Value > 0 And Target.Value < 19 Then
         Rows(Target.Value + 101 & ":119").Hidden = True
      End If
   End If
End Sub

 

[neilcsmith1984]

Hi All,

I found the solution

 

[neilcsmith1984]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$A$99" Then
      Rows("102:119").Hidden = False
      If Target.Value > 0 And Target.Value < 19 Then
         Rows(Target.Value + 101 & ":119").Hidden = True
      End If
   End If
End Sub

This is great, thanks... works perfectly

 

[Fluff]

You're welcome & thanks for the feedback.

 

[neilcsmith1984]

Hi mate,

The good for some reason has stopped working when the target cell auto updates.

I now have to click on that cell for it to then hide or unhide new rows

I am new to vba, I am not sure what has happened, I am hoping you can help.

Thanks
N

 

[Fluff]

How is the cell being changed?

 

[neilcsmith1984]

The cell A99 contains a formula that counts matching rows of a table, based on a selection of a combo box.

When I chose a product name from a combo box, the formula in A99 updates the amount of rows in a table that match that product name.

formula in a99 is =countifs(FC[product],b5,fc[market]=b6)

B5 is linked to a combo box

 

[Fluff]

In that case you cannot use a change event, as it's not triggered by the result of a formula.
What type of combo are you using & do you have any code behind it?

 

[neilcsmith1984]

The combo is a combo box (active x control) which is linked to a named formula PRODNAME,

PRODNAME is the following formula =OFFSET(Sheet1!$B$2,0,0,COUNT(IF(Sheet1!$B$2:$B$100="","",1)),1)