Specific macro or VBA code to hide and unhide rows

Hey everyone,

This is my first question here, but I used the forum a lot before. Each time I found the answer, so I have good hopes for this one too
I found a couple of similar threads about this, but none worked for me (probably because I don't know how to adapt the code).

I have an empty col A
I have a col B with names in it.
I have a lot of other col from C to ... but those don't really mather here

In col B, beside names, I have empty cells too ... These are for the users to add names.
e.g.
B3 to B11 = names already filled in
B12 to B17 = empty cells

My question about this is:

If B12 is empty, B13 till B17 should be hidden rows.
From the moment someone fills in B12, B13 shoud auto unhide, B14 till B17 stays hiden.
If someone wants to fill in an extra name in B13 too, B14 = unhide, B15 till B17 = hiden
And so on, and so on ... Note: B18: always visible because that's a new set of values that always must be visible.

Hope that someone gets me, and that my English wasn't too bad
PS: it's for Excel that I'm using @ work, so it's 2007.
The attachment is another version of Excel, that's my home edition.

Thanks in advance !

Kristof

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    If Not Intersect(Target, Range("B12:B17")) Is Nothing Then
        Range("B13:B17").EntireRow.Hidden = True
        For Each c In Range("B12:B17")
            If Not IsEmpty(c) Then c.Resize(2, 1).EntireRow.Hidden = False
        Next c
    End If
End Sub

Hi louvaek

Welcome to the Forum!!!

Here's another approach

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim OldValue As String, NewValue As String
  If Target.Cells.Count > 1 Then Exit Sub
  If Not Target.Column = 2 Then Exit Sub
  With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Undo
    OldValue = Target.Value  'store old value
    If Not OldValue = "" Then
      .Undo
      .ScreenUpdating = True
      .EnableEvents = True
      Exit Sub
    Else
      .Undo
      NewValue = Target.Value     'store new value
      Range(Target.Address).Offset(1, 0).EntireRow.Hidden = False
      .EnableEvents = True
      .ScreenUpdating = True
    End If
  End With
End Sub

Hi louvaek

Here's your File with the Code embedded...

Jaslake,

I had to change my sheet and I don't know what I have to change in your code to make it work again, because now it doesn't :s
In fact, it no longer has to check one column, it has to check five columns.

Row 11 = always visible, value is fixed
Row 12 = always visible, is empty by default (user can input name or names in the range C12:G12
Row 13 = NOT visible, unless user has made an input in one (or more or all) of these cells: C12, D12, E12, F12 or G12
Row 14 = NOT visible, unless user has made an input in one (or more or all) of these cells: C13, D13, E13, F13 or G13
...
Row 21 = NOT visible, unless user has made an input in one (or more or all) of these cells: C20, D20, E20, F20 or G20
Row 22 = always visible, value is fixed

Same for all the other empty lines in the sheet.

I also have a VBA that stopped working when I copy pasted your code (the VBA code to 'UPPERCASE input user)

Hope you can help me once again I'll attach my sheet ...
I would be very thankfull

Grt
Kristof

Hi Kristof

This is a major rewrite and you've marked your Thread as SOLVED.

I'd suggest you get your Worksheet where you need it to be (to avoid rewrites) then start a new Thread for these new requirements.

Having done so, if you wish, you may PM me with a link to your new Thread and I'll look at it.

Hi Kristof

Been a busy Family weekend...