VBA to hide/show rows based on cell on another tab

[Sunny London]

I've been looking through lots of other posts about showing and hiding rows and have been trying to amend the code to work on my spreadsheet, but don't know enough about VBA and can't make it work. Can anyone help please?

On my Data Input tab, if cell B12 is empty then I want rows 14,34 and 37 on my Bal Sht tab to hide automatically. If cell B12 on the Data Input tab has anything in it, then rows 14,34 & 37 on my Bal Sht tab should show.

Also, if cell B13 is empty then rows 15,35 & 38 should be hidden or if B13 has anything in it, then rows 15,35 & 38 should be visible.

Any suggestions?
Thanks

 

[VoG]

Try

Sub HideRows()
With Sheets("Bal Sht")
    .Rows(14).Rows.Hidden = Sheets("Data Input").Range("B12").Value = ""
    .Rows(34).Rows.Hidden = Sheets("Data Input").Range("B12").Value = ""
    .Rows(37).Rows.Hidden = Sheets("Data Input").Range("B12").Value = ""
    .Rows(15).Rows.Hidden = Sheets("Data Input").Range("B13").Value = ""
    .Rows(35).Rows.Hidden = Sheets("Data Input").Range("B13").Value = ""
    .Rows(38).Rows.Hidden = Sheets("Data Input").Range("B13").Value = ""
End With
End Sub

 

[Sunny London]

Thanks Peter, I tried this by right-clicking on the Data Input tab and selecting View Code (because that makes the macro run authomatically, is that right?) and I pasted it there but it didn't work.

I also tried it in the Bal Sht tab too. And I tried it as "Private Sub Hide Rows()" but none of these options worked.

What am I doing wrong?

 

[VoG]

Press ALT + F11 to open the Visual Basic Editor, select Module from the Insert Menu and paste in the code. Press ALT + Q to close the code window.

Press ALT + F8, click on HideRows then click the Run button.

 

[Sunny London]

Is there any way this macro can run automatically, without the need to press Alt and F8?

 

[VoG]

Do you want it to run automatically when B12 or B13 on the Data Input sheet change? If so, do you change those cells or do they contain formulas?

 

[Sunny London]

Yes, the rows should show/hide when cells B12 or B13 change.

B12 and B13 won't contain formulas, just names will be typed in.

If a name has been typed into B12 or B13 I want those rows on Bal Sht to show. Or if a name in B12 or B13 is deleted so that B12 or B13 are empty, the rows on Bal Sht should be hidden.

 

[VoG]

Try this: right click the Data Input tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("Bal Sht")
    Select Case Target.Address(False, False)
        Case "B12"
            .Rows(14).Rows.Hidden = Target.Value = ""
            .Rows(34).Rows.Hidden = Target.Value = ""
            .Rows(37).Rows.Hidden = Target.Value = ""
        Case "B13"
            .Rows(15).Rows.Hidden = Target.Value = ""
            .Rows(35).Rows.Hidden = Target.Value = ""
            .Rows(38).Rows.Hidden = Target.Value = ""
    End Select
End With
End Sub

 

[Sunny London]

Perfect! That's exactly what I need. Thank you very much.

 

[Sunny London]

One other related query - if I wanted to hide a series of adjacent rows (instead of individual rows), I thought that the VBA might be:

[VBA]
.Rows(37:40).Rows.Hidden = Target.Value = ""
[/VBA]

or
[VBA]
.Rows(37 - 40).Rows.Hidden = Target.Value = ""
[/VBA]

but neither option works, so I'm wondering what I'm doing wrong.