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

criner5

New Member
Joined
Aug 26, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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
VBA Code:
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,624
Try this:

Second code is unhiding the rows the First code hides

VBA Code:
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

Well-known Member
Joined
Feb 6, 2012
Messages
1,624
Or even:

VBA Code:
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

New Member
Joined
Aug 26, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Thank you! I can't believe I missed something so obvious. Your solution works perfectly.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,608
Messages
5,597,143
Members
414,129
Latest member
mr vba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top