VBA to unhide lines after cell entry

andrewb90

Well-known Member
Joined
Dec 16, 2009
Messages
1,077
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Use the Worksheet_Change event

Code:
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
 
Upvote 0
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?
 
Upvote 0
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)

Rich (BB code):
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
 
Upvote 0
What if you wanted to go the other way, so that if a value in column B was deleted the row would hide?
 
Upvote 0
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?
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top