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

 

[Chili14]

It works for some combinations but not all. As one example - If all items are checked on the "List" except "Cleaning Oven" and "Ultrasonic it will only hide rows if the macro is run 2x, or every other time. Very strange. There are other combinations that behave the same.

 

[Chili14]

I changed the Range property to UsedRange property to search for the last row and it seems to be working now.

bottomA = desWS.UsedRange.Rows(desWS.UsedRange.Rows.Count).Row

I can't thank you enough.

 

[mumps]

Glad it worked out.