Find range of blank rows and hide them, within a worksheet change event

[cdown16]

Hello, I am giving up and posting as I cannot get the second part of this code to work within a Worksheet change, but it works when placed in a separate macro that I manually call. I need this whole code to run during a cell change. The Pivot Table piece works, everything after with hiding/unhiding rows does not.

I am finding a range of blank rows between two pivot tables and wanting to hide those rows. As we know, pivot table row counts vary on selection so it has to stay dynamic, but then unhide those rows before it starts searching for blank rows again. Below the dotted line is where I need some help on why within a worksheet change, is this part not running. THANKS!



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'This line stops the worksheet updating on every change, it only updates when cell
'is touched
If Intersect(Target, Worksheets(AMPS).Range("C388")) Is Nothing Then Exit Sub


'Set the Variables to be used
Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
Dim rngstart As Range
Dim rnghide As Range
Dim lcell As String
Dim ecell As String



'Here you amend to suit your data
Set pt = Worksheets(AMPS).PivotTables("PivotTable3")
Set Field = pt.PivotFields("Category New")
NewCat = Sheets("AMPS").Range("c388").Value


'This updates and refreshes the PIVOT table
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With


-------------------------------------------------------------Need help below.
Sheets("AMPS").Rows("5:386").Hidden = False


Set rngstart = Range("a5")
Set rnghide = rngstart.End(xlDown).Offset(1, 0)
lcell = rngstart.End(xlDown).Offset(1, 0).Row
ecell = rnghide.End(xlDown).Offset(-1, 0).Row

If NewCat = "COLD CEREAL" Then
Sheets("AMPS").Rows(lcell & ":" & ecell).Hidden = True
ElseIf NewCat = "FROZEN BREAKFAST" Then
Sheets("AMPS").Rows(lcell & ":" & ecell).Hidden = True
ElseIf NewCat = "PWS" Then
Sheets("AMPS").Rows(lcell & ":" & ecell).Hidden = True
ElseIf NewCat = "*******" Then
Sheets("AMPS").Rows(lcell & ":" & ecell).Hidden = True
ElseIf NewCat = "TOASTER PASTRY" Then
Sheets("AMPS").Rows(lcell & ":" & ecell).Hidden = True
Else
Sheets("AMPS").Rows(lcell & ":" & ecell).Hidden = False
End If


End Sub

 

[cdown16]

I even tried to really simplify it and use:

Range("A6:A386").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

and no rows hide in the event either. Any help is greatly appreciated.