VBA to unhide lines after cell entry

[andrewb90]

Hello,

I'm not sure if this is possible, but I would like to have a code that when anything is typed into B12 or C12 then row 13 will unhide, then the same thing would happen with B13 & C13 and row 14 and so on all the way through row 30 unhiding. To clarify, if there is any text in either cell, then the following row should be visible.

Thanks,

Andrew

 

[ozbod]

Use the Worksheet_Change event

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 2 Or Target.Column = 3 Then 'column B or C has changed
        Rows(Target.row + 1).EntireRow.Hidden = False    'Unhide the next row (even if it is already visible)
    End If
    
    Cells(Target.row, Target.Column).Select  'Go back to the cell you edited
    
End Sub

 

[Peter_SSs]

Referring to columns B:C only, let's suppose entries have been typed into rows 12, 13, 14, 15 and 16 so they are all visible and so is row 17 according to your request.

a) Do you want anything to happen (eg hide row 17) if the values in row 16 are now deleted?

b) Do you want anything to happen if the values in, say, row 13 are now deleted? If so, what?

 

[andrewb90]

Peter,

If information is deleted, nothing needs to happen.

 

[Peter_SSs]

If information is deleted, nothing needs to happen.

OK, then I would use this Worksheet_Change code. Test in a copy of your workbook.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window & test.

4. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, rRws As Range
  
  Set Changed = Intersect(Target, Range("B12:C29"))
  If Not Changed Is Nothing Then
    For Each rRws In Changed.Rows
      rRws.Offset(1).Hidden = False
    Next rRws
  End If
End Sub

 

[helpexcel]

What if you wanted to go the other way, so that if a value in column B was deleted the row would hide?

 

[Peter_SSs]

What if you wanted to go the other way, so that if a value in column B was deleted the row would hide?

Hide which row?
- the one the value was deleted from, or
- the row below like the original question was about?

 

[helpexcel]

I ended up putting numbers in column A if there was data in column B. I'm sure there is a better way to do it.

Dim Changed As Range, rRws As Range
Set ws1 = ThisWorkbook.Sheets("Sheet1")

Set Changed = Intersect(Target, wsSP.Range("B10:B50"))
If Not Changed Is Nothing Then
For Each rRws In Changed.Rows
rRws.Offset(1).Hidden = False
Next rRws
End If

Dim X As Range
For Each X In sht.Range("A10:A50")
If X = "" Then
X.EntireRow.Hidden = True
Else
X.EntireRow.Hidden = False
End If
Next X