Unhide specified number of rows

[Giofan]

Hi,

I am very new to VBA, so please be gentle.

I am looking for some code that will unhide a specified number of rows, dependant on a Cell value. For Example,

If "B2" states 2, then 2 rows will become unhidden, starting from row 2 inclusive (2:3). If "B2" states 5, then 5 rows will become unhidden, starting from row 2 inclusive (2:6).

Thanks for your patience.

Giofan

 

[Fluff]

If row 2 is hidden, how are you changing the value in B2?

 

[Giofan]

Hi Fluff,

Sorry, my Bad "B1".

 

[Fluff]

OK, try this

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Target.Address = "$B$1" Then Exit Sub
   Rows("2:" & Target.Value + 1).Hidden = False
End Sub

This needs to go in the relevant sheet module

 

[Giofan]

Hi Fluff,

Thanks so mych. I have copied in to the relevant module, however I am getting a 'Run-Time error '424': Object required' error; an ideas what this could be?

Many thanks in advance.

Giofan

 

[Fluff]

When you click debug, which line of code is highlighted?

 

[Giofan]

Hi Fluff,

Sorry, it didn't recognise the Sub in the Macro Box (assuming this is because it is a Private Sub), and so when I go to run the code, it prompts me to save it, which starts a New Sub. Apologies if I am making basic errors here. The error only happens if I make the Private Sub a Sub and change the (ByVal Target as Range) to (), as without it still doesn't recognise as a Sub.

Apologies for my ramblings, hope you can help. No worries if not. Thanks again in advance.

Giofan

 

[Fluff]

Leave it exactly as it was & when you change the value in B1 the rows should automatically unhide.