Auto Hiding and Unhiding Rows

[SONART]

Hi,

I'm just a novice when it comes to VBA.

I have picked up a macro online but it needs tweaking to auto hide and unhide rows with 0 values.

The macro highlighted in green works and enables me to select values from a drop down list which drives my derived data set.

I'm trying to link a macro to auto hides and unhides rows with 0 in column A, triggered when selecting from the drop down list.
I also want the macro to default or reset to a starting point (A23) and looks at column A, auto hide/unhide the rows with 0 value.

The macro below (highlightred in red) appears to half work but it appears it doesn't reset to A23 before running the command and I think it's driven by keying in 0 to a cell range. I want the macro to be automated when selecting from a drop down value (target row 13 and column 13).

Could someone please help.

Thanks in advance,
SONART

Private Sub Worksheet_Change(ByVal Target As Range)

' Command below to stop it from flickering
Application.ScreenUpdating = False
If Target.Row = 13 And Target.Column = 13 Then
'calculate criteria cell in case calculation mode is manual
Worksheets("UNIQUE").Range("G4").Calculate
Worksheets("UNIQUE").Range("BRK_UNIQUE") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("UNIQUE").Range("G3:G4"), _
CopyToRange:=Sheets("UNIQUE").Range("O8:T8"), Unique:=True

End If

Dim rngData As Range, rngCell As Range, xlCalc As XlCalculation
Set rngData = Range("A23:A82")
If Not Intersect(Target, rngData) Is Nothing Then
With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
For Each rngCell In rngData.Cells
rngCell.EntireRow.Hidden = rngCell.Value = 0
Next rngCell
.Calculation = xlCalc
.ScreenUpdating = True
.EnableEvents = True
End With
End If
Set rngData = Nothing
End Sub

 

[Trebor76]

Thread duplicated here