Auto hide/unhide based on input

**UnSpoknOne** · 11-02-2011, 05:42 PM

Hello everyone,

I need help with a vba for a spreadsheet I am working on.

My dilemma is I want to put a value say in cell A10 and as soon I press tab, I want to unhide Row 11. In turn when I write a value in cell A11 press tab I want row 12 to unhide.

For now I want to hide rows 39:139 and start unhiding them one by one as soon as I start there is a value in cell A38.

I've search online and I get very close results, but nothing that does what I need.

Any help in the right direction would be greatly appreciated.

Regards,

UnSpoknOne

**Leith Ross** · 11-02-2011, 10:29 PM

Hello UnSpoknOne,

Welcome to the Forum!

Add this macro to the worksheet with the hidden rows.


Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("A38:A" & Rows.Count)) Is Nothing Then
       If Rows(Target.Row + 1).Hidden = True Then Rows(Target.Row + 1).Hidden = False
    End If
    
End Sub

How to Save a Worksheet Event Macro

Copy the macro using CTRL+C keys.
Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
Left Click on View Code in the pop up menu.
Paste the macro code using CTRL+V
Save the macro in your Workbook using CTRL+S

**UnSpoknOne** · 11-03-2011, 05:33 PM

Originally Posted by Leith Ross

Hello UnSpoknOne,

Welcome to the Forum!

Add this macro to the worksheet with the hidden rows.


Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("A38:A" & Rows.Count)) Is Nothing Then
       If Rows(Target.Row + 1).Hidden = True Then Rows(Target.Row + 1).Hidden = False
    End If
    
End Sub

How to Save a Worksheet Event Macro

Copy the macro using CTRL+C keys.
Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
Left Click on View Code in the pop up menu.
Paste the macro code using CTRL+V
Save the macro in your Workbook using CTRL+S

Thank you Leith Ross, so very much!!! It worked exactly how I wanted it!!!
If you can help me with one more thing, how can I get it to autohide when I delete the value in cell A38.
Say I delete what's in that cell. I want row 39 to hide again.

In the mean time I will play with the code to get it to hide again.

Thank you so very much.
Regards.

**Leith Ross** · 11-03-2011, 05:52 PM

Hello UnSpoknOne,

This version of the macro will hide or unhide the next row based on the content of the previous cell.


Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("A38:A" & Rows.Count)) Is Nothing Then
       If Target.Value = "" Then
          Rows(Target.Row + 1).Hidden = True
       Else
          Rows(Target.Row + 1).Hidden = False
       End If
    End If
    
End Sub

**UnSpoknOne** · 11-03-2011, 06:09 PM

Thank you once again, especially for the quick response. Now I can do the other complicated things I wanted to do. lol.

I can do most everything in excel except for vba macros. So thanks once again.

Best Regards,

UnSpoknOne