Automatically Hide Row on Another Worsheet

[Chili14]

Could someone help me write a script that will automatically hide rows on a "Summary" worksheet base on selections made from a "List" worksheet. The calculated values in column A ("QTY") on the "Summary" worksheet are "" when not selected from the "List" worksheet. I found some script that I modified to work looking at the calculated value "" but it only runs when selecting a cell or hitting Enter when in the "Summary" worksheet. Ideally I would like the "Summary" Worksheet to update as soon as the selection is made on the "List" worksheet so when viewing in split screen mode the "Summary" worksheet is updated automatically, without having to select a cell or hitting enter. Is this possible?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Cells.Find("""", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim cell As Range
ActiveSheet.UsedRange.Rows.EntireRow.Hidden = False
For Each cell In Range("a4:a" & LastRow)
If cell.Value = "" Then
cell.EntireRow.Hidden = True
End If
Next cell
Application.ScreenUpdating = True
End Sub

 

[mumps]

If I understood correctly, you have formulas in column A of the "Summary" sheet. These formulas calculate when you select a cell in the "List" sheet. When a selection is made in the "List" sheet, you want all rows in column A of the "Summary" that are blank to be hidden. Is this what you want to do?

 

[Chili14]

That's correct. To be clear the formulas in row A of the "Summary" sheet returns "" when True. I will add that I'm only hiding rows on the "Summary" sheet with "", and between A4 and "last Row". My actual last Row is the summation of all the columns and does not have "the formula".

 

[mumps]

Place this macro in the code module for the "Summary" worksheet.

Private Sub Worksheet_Calculate()
    Application.ScreenUpdating = False
    Dim LastRow As Long, cel As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ActiveSheet.UsedRange.Rows.EntireRow.Hidden = False
    For Each cel In Range("A4:A" & LastRow - 1)
        If cel.Value = "" Then
            cel.EntireRow.Hidden = True
        End If
    Next cel
    Application.ScreenUpdating = True
End Sub

 

[Chili14]

Got a run error at:
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 

[mumps]

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[Chili14]

Does this work?

https://www.dropbox.com/s/ew63kow3j76qoy3/LBS-100 OCE Select Auto Hide.xls?dl=0

 

[mumps]

I can't see how any cell in column A of the "Summary" sheet will ever be blank. When you put a check mark beside a product in the List sheet, the product is added to the Summary sheet. When you un-check the product in List, it is deleted form the Summary sheet. When will any cell in column A of the "Summary" sheet be blank?

 

[Chili14]

Yes, the evaluated value in Row A of the "Summary" sheet returns "" if true. I assumed this was a blank cell. What's weird is that it works when using the SelectChange event but not the Change or Calculate event. Can you offer any other ideas or options?

 

[mumps]

This macro works for me.

Private Sub Worksheet_Calculate()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim bottomA As Long, cel As Range, desWS As Worksheet
    Set desWS = Sheets("Summary")
    bottomA = desWS.Range("A" & desWS.Rows.Count).End(xlUp).Row
    On Error GoTo errHandler
    desWS.Cells.EntireRow.Hidden = False
    For Each cel In desWS.Range("A5:A" & bottomA - 1)
        If cel.Value = "" Then
            cel.EntireRow.Hidden = True
        End If
    Next cel
errHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub