ExcelBanter - VBA to hide a row in different worksheet

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - VBA to hide a row in different worksheet (https://www.excelbanter.com/excel-discussion-misc-queries/135323-vba-hide-row-different-worksheet.html)

VBA to hide a row in different worksheet

I need a VBA code to automatically hide or unhide a row in one sheet based
upon the value is a cell on another sheet.

Example:

If Sheet1, cell A1 = 0, then Sheet2, row 5 is hiden.
If Sheet1, cell A1 0, then Sheet2, row 5 is visible.

Suggestions?

Answer: VBA to hide a row in different worksheet

Formula:




[list=1][*]Open your Excel workbook and press Alt + F11 to open the Visual Basic Editor.[*]In the Project Explorer window, find the worksheet where you want to monitor for changes and double-click it to open the code window.[*]Copy and paste the code above into the code window.[*]Change the cell reference in the code to the cell you want to monitor for changes. In this example, it's set to monitor cell A1 on Sheet1.[*]Save your workbook and close the Visual Basic Editor.[/list]

Here's a VBA code that will do what you're looking for:

Formula:




Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then 'Change this to the cell you want to monitor for changes
        If Target.Value = 0 Then
            Sheets("Sheet2").Rows(5).Hidden = True
        Else
            Sheets("Sheet2").Rows(5).Hidden = False
        End If
    End If
End Sub

Now, whenever the value in the cell you're monitoring changes, the code will check if it's equal to 0. If it is, it will hide row 5 on Sheet2. If it's greater than 0, it will unhide row 5 on Sheet2.

VBA to hide a row in different worksheet

Try: will unhide if not 0 (blank or < 0)

Sub HideRow()

If Worksheets("sheet1").Range("A1") = 0 Then
Worksheets("sheet2").Rows(5).EntireRow.Hidden = True
Else
Worksheets("sheet2").Rows(5).EntireRow.Hidden = False
End If

End Sub

"TDC" wrote:

I need a VBA code to automatically hide or unhide a row in one sheet based
upon the value is a cell on another sheet.

Example:

If Sheet1, cell A1 = 0, then Sheet2, row 5 is hiden.
If Sheet1, cell A1 0, then Sheet2, row 5 is visible.

Suggestions?

VBA to hide a row in different worksheet

for the row to automatically get hidden a Worksheet change Event need to be
added into the code. Install as follows

1) right click tab on bottom of worksheet labeled Sheet1.
2) Select view Code
3) Copy subroutine below and paste into VBA Code window.

Sub Worksheet_Change(ByVal Target As Range)

If (Target.Row = 1) And (Target.Column = 1) And _
(Target = 0) Then

Worksheets("sheet2").Rows(5).EntireRow.Hidden = True
Else
Worksheets("sheet2").Rows(5).EntireRow.Hidden = False
End If
End Sub

"TDC" wrote:

I need a VBA code to automatically hide or unhide a row in one sheet based
upon the value is a cell on another sheet.

Example:

If Sheet1, cell A1 = 0, then Sheet2, row 5 is hiden.
If Sheet1, cell A1 0, then Sheet2, row 5 is visible.

Suggestions?