none
Auto hide rows based on another cell value from another sheet within the workbook

    Question

  • I have up to 42 cells that I want to hide based on another cells value. I am looking to have this work automatically based on another cells value:

    I want to hide row 23 in sheet1 if cell Sheet2!A1 is blank

    I want to hide row 24 in sheet1 if cell Sheet2!A2 is blank
    I want to hide row 25 in sheet1 if cell Sheet2!A3 is blank

    and so on...

    This will be a template so I can pre-hide the 42 cells if that would work better and then when a value is entered for example in Sheet2!A1, row 23 on Sheet1 can unhide...

    Please help! I appreciate any time you can take to assist me with this!


    Dustin Shepard

    Sunday, September 09, 2012 11:43 PM

Answers

  • Someone else helped me with this, if anyone needs this it looks like this (we added to it a little bit):

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyCells As Range

    Set MyCells = Range("A1:C42")

    If Intersect(Target, MyCells) Is Nothing Then Exit Sub
    If Target.Column = 2 Then Exit Sub
    If Target.Count > 1 Then Exit Sub

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    If Target.Column = 1 Then
      Worksheets("Sheet1").Rows(22 + Target.Row).Hidden = Target.Value = ""
    Else
      Worksheets("Sheet1").Rows(98 + Target.Row).Hidden = Target.Value = ""
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub


    Dustin Shepard

    • Marked as answer by Dustin Shepard Tuesday, September 11, 2012 2:38 AM
    Tuesday, September 11, 2012 2:37 AM

All replies

  • Use your macro recorder to record these actions and see what you get.
    Monday, September 10, 2012 12:55 AM
  • check this link this may help you out

    http://www.excelforum.com/excel-programming-vba-macros/774004-autohide-row-based-on-certain-cell-value.html

    Monday, September 10, 2012 6:32 AM
  • Unfortunately this link you provided does not help me as this is for specific values to hide or unhide. I do not have specific values, I need the rows to hide if blank and unhide if anything is typed into the cell.

    I do not know how a macro recorder would help here? Do you have specific instructions? I thought a VBA script would be best?


    Dustin Shepard

    Monday, September 10, 2012 8:39 PM
  • Someone else helped me with this, if anyone needs this it looks like this (we added to it a little bit):

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyCells As Range

    Set MyCells = Range("A1:C42")

    If Intersect(Target, MyCells) Is Nothing Then Exit Sub
    If Target.Column = 2 Then Exit Sub
    If Target.Count > 1 Then Exit Sub

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    If Target.Column = 1 Then
      Worksheets("Sheet1").Rows(22 + Target.Row).Hidden = Target.Value = ""
    Else
      Worksheets("Sheet1").Rows(98 + Target.Row).Hidden = Target.Value = ""
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub


    Dustin Shepard

    • Marked as answer by Dustin Shepard Tuesday, September 11, 2012 2:38 AM
    Tuesday, September 11, 2012 2:37 AM