VBA to hide cells in two sheets with different criteria for each sheet

[criner5]

Hi I'm trying to create a tracker and there are certain data that pull in that I want to hide.

In sheet one, I need to hide rows that say "Not a Court Deadline" so that only the court deadlines remain in view.
In sheet two, I need to hide any row where in column E, "Complete" has been entered.

I need this to run when the sheet is closed.

Here is the code I have. It will run the code when I close but it only accomplishes one of the tasks. Any help is much appreciated.

Option Compare Text

Sub Hide_Row1()

Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In Worksheets
beginRow = 1
endRow = 100
chkCol = 5
For RowCnt = beginRow To endRow
If sht.Cells(RowCnt, chkCol).Value = "COMPLETE" Then
sht.Cells(RowCnt, chkCol).EntireRow.Hidden = True
Else
sht.Cells(RowCnt, chkCol).EntireRow.Hidden = False
End If
Next RowCnt
Next sht
Call Hide_Row2
End Sub

Sub Hide_Row2()
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In Worksheets
beginRow = 1
endRow = 100
chkCol = 2
For RowCnt = beginRow To endRow
If sht.Cells(RowCnt, chkCol).Value = "Not Court Deadline" Then
sht.Cells(RowCnt, chkCol).EntireRow.Hidden = True
Else
sht.Cells(RowCnt, chkCol).EntireRow.Hidden = False
End If
Next RowCnt
Next sht
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'to run a macro named Hide_Row
Call Hide_Row1

End Sub

 

[mrshl9898]

Try this:

Second code is unhiding the rows the First code hides

Sub Hide_Row1()

Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In Worksheets
beginRow = 1
endRow = 100
chkCol = 5
For RowCnt = beginRow To endRow
If sht.Cells(RowCnt, chkCol).Value = "COMPLETE" Then
sht.Cells(RowCnt, chkCol).EntireRow.Hidden = True
Else
sht.Cells(RowCnt, chkCol).EntireRow.Hidden = False
End If
Next RowCnt
Next sht
Call Hide_Row2
End Sub

Sub Hide_Row2()
Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In Worksheets
beginRow = 1
endRow = 100
chkCol = 2
For RowCnt = beginRow To endRow
If sht.Cells(RowCnt, chkCol).Value = "Not Court Deadline" Then
sht.Cells(RowCnt, chkCol).EntireRow.Hidden = True
End If
Next RowCnt
Next sht
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'to run a macro named Hide_Row
Call Hide_Row1

End Sub

 

[mrshl9898]

Or even:

Sub Hide_Row1()

Dim sht As Worksheet
Application.ScreenUpdating = False
For Each sht In Worksheets
beginRow = 1
endRow = 100
chkCol = 5
chkCol2 = 2
For RowCnt = beginRow To endRow
If sht.Cells(RowCnt, chkCol).Value = "COMPLETE" Or sht.Cells(RowCnt, chkCol2).Value = "Not Court Deadline" Then
sht.Cells(RowCnt, chkCol).EntireRow.Hidden = True
Else
sht.Cells(RowCnt, chkCol).EntireRow.Hidden = False
End If
Next RowCnt
Next sht
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'to run a macro named Hide_Row
Call Hide_Row1

End Sub

 

[criner5]

Thank you! I can't believe I missed something so obvious. Your solution works perfectly.