Unhide below row if above row is populated VBA

[ALAMZEB]

Hi

I have template where 90 rows can be populated based on product, but not all customers buy all 90. Is there any event generated macro if row is populated ,a row below become visible and so on.

Its a project and really needs to impress management

 

[Luke M]

How about this little snippet? Right-click on sheet tab, view code. Paste this in.

Private Sub Worksheet_Change(ByVal Target As Range)
'Undhide row below changed cell
Target.Offset(1).EntireRow.Hidden = False
End Sub

 

[ALAMZEB]

HI Luke M.. you are legend,
thats works like charm but i want to start it from A17

 

[Luke M]

Perhaps something like this then?

Private Sub Worksheet_Change(ByVal Target As Range)
'Do nothing unless we changed something in rows 17:1000
'Adjust if needed
If Intersect(Target,Range("17:1000")) Is Nothing Then Exit Sub
'Unhide row below changed cell
Target.Offset(1).EntireRow.Hidden = False
End Sub

If you want to further limit it to being a change in col A, change the Intersect line to check Range("A17:A1000")

 

[ALAMZEB]

Hi Luke
Its works great, can’t be done more perfectly
But sorry if I am pushing
I was wondering if I can go bit advance level (advance for me not for you)
Can I used spin button to do the same
Let say I have spin button in A17 with reference sell in B17
Whatever number is in reference cell (B17) using spin button it will unhide that numbers of rows
Thanks in advance

 

[Luke M]

Sure thing. That's actually a simpler setup to do. Build the spin box form, and then put this short macro in a regular code module. Right-click on spin box, assign macro, choose "UnhideRows" and you're good to go.

Sub UnhideRows()
Dim NumRows As Long
NumRows = Range("B17").Value

Application.ScreenUpdating = False
'First, hide all the rows
Range("18:1018").EntireRow.Hidden = True
'Next, check if we need to unhide anything
If NumRows > 0 Then
    Range("A18").Resize(NumRows).EntireRow.Hidden = False
End If
Application.ScreenUpdating = True
End Sub

 

[ALAMZEB]

Thanks for prompt response, that solved my problem

much appreciated