Sub Worksheet_DeactivateV3()
'
Dim ArrayRow As Long, ProposalRow As Long
Dim FullRangeOfRowsToBeHidden As Range, FullRangeOfRowsToBeUnhidden As Range
Dim RangeRow As Range
Dim RemainingRangeOfRowsToBeHidden As Range, RemainingRangeOfRowsToBeUnhidden As Range
Dim SpecsColumn_O_Array As Variant
Dim proposalSheet As Worksheet
'
Application.ScreenUpdating = False ' Turn ScreenUpdating off
Application.Calculation = xlCalculationManual ' Turn calculation mode off
'
SpecsColumn_O_Array = Sheets("SPECS-P").Range("O4:O204").Value2 ' <--- Set this to the range of cell numbers in Column 'O' of 'SPECS-P'
'
Set proposalSheet = Sheets("Proposal-P")
'
For ArrayRow = 1 To UBound(SpecsColumn_O_Array, 1) ' Loop through the rows of SpecsColumn_O_Array
ProposalRow = ArrayRow + 144 ' Save the row offset for 'Proposal-P' into ProposalRow
'
'''''''''''''''''''''''''''''''''''''''''
' Handle the single Row Hiding/Unhiding '
'''''''''''''''''''''''''''''''''''''''''
If ArrayRow <> 1 Then ' If ArrayRow is not the first row in SpecsColumn_O_Array then ...
If SpecsColumn_O_Array(ArrayRow, 1) = 0 And _
Not IsEmpty(SpecsColumn_O_Array(ArrayRow, 1)) Then ' If the value in SpecsColumn_O_Array = 0 then ...
If Not FullRangeOfRowsToBeHidden Is Nothing Then ' If we have already saved a row number to FullRangeOfRowsToBeHidden then ...
Set FullRangeOfRowsToBeHidden = Union(FullRangeOfRowsToBeHidden, _
proposalSheet.Rows(ProposalRow)) ' Add the ProposalRow to the rows already in FullRangeOfRowsToBeHidden
Else ' Else ...
Set FullRangeOfRowsToBeHidden = proposalSheet.Rows(ProposalRow) ' Save the ProposalRow to FullRangeOfRowsToBeHidden
End If
ElseIf SpecsColumn_O_Array(ArrayRow, 1) > 0 Then ' Else if the value in SpecsColumn_O_Array > 0 then ...
If Not FullRangeOfRowsToBeUnhidden Is Nothing Then ' If we have already saved a row number to FullRangeOfRowsToBeUnhidden then ...
Set FullRangeOfRowsToBeUnhidden = Union(FullRangeOfRowsToBeUnhidden, _
proposalSheet.Rows(ProposalRow)) ' Add the ProposalRow to the rows already in FullRangeOfRowsToBeUnhidden
Else ' Else ...
Set FullRangeOfRowsToBeUnhidden = proposalSheet.Rows(ProposalRow) ' Save the ProposalRow to FullRangeOfRowsToBeUnhidden
End If
End If
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Handle 'SPECS-P' $O4 value where 4 rows will be hidden/unhidden '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Else ' Else ...
If SpecsColumn_O_Array(ArrayRow, 1) = 0 And _
Not IsEmpty(SpecsColumn_O_Array(ArrayRow, 1)) Then ' If the value in SpecsColumn_O_Array = 0 then ...
If Not FullRangeOfRowsToBeHidden Is Nothing Then ' If we have already saved a row number to FullRangeOfRowsToBeHidden then ...
Set FullRangeOfRowsToBeHidden = Union(FullRangeOfRowsToBeHidden, _
proposalSheet.Rows("142:145")) ' Add the ProposalRow to the rows already in FullRangeOfRowsToBeHidden
Else ' Else ...
Set FullRangeOfRowsToBeHidden = proposalSheet.Rows("142:145") ' Save row range of 142:145 to FullRangeOfRowsToBeHidden
End If
ElseIf SpecsColumn_O_Array(ArrayRow, 1) > 0 Then ' Else if the value in SpecsColumn_O_Array > 0 then ...
If Not FullRangeOfRowsToBeUnhidden Is Nothing Then ' If we have already saved a row number to FullRangeOfRowsToBeUnhidden then ...
Set FullRangeOfRowsToBeUnhidden = Union(FullRangeOfRowsToBeUnhidden, _
proposalSheet.Rows("142:145")) ' Add the row range of 142:145 to the rows already in FullRangeOfRowsToBeUnhidden
Else ' Else ...
Set FullRangeOfRowsToBeUnhidden = proposalSheet.Rows("142:145") ' Save row range of 142:145 to FullRangeOfRowsToBeUnhidden
End If
End If
End If
Next ' Loop back
'
''''''''''''''''''''''''''
' Smart Hide/Unhide Rows '
''''''''''''''''''''''''''
If Not FullRangeOfRowsToBeHidden Is Nothing Then ' If there are rows that should be hidden then ...
On Error Resume Next ' In the case that there are no ranges of rows that need to be hidden, skip error
Set RemainingRangeOfRowsToBeHidden = _
FullRangeOfRowsToBeHidden.SpecialCells(xlCellTypeVisible) ' Get the range of visible rows that haven't been hidden yet
On Error GoTo 0 ' Return error handling back over to Excel
'
If Not RemainingRangeOfRowsToBeHidden Is Nothing Then _
RemainingRangeOfRowsToBeHidden.EntireRow.Hidden = True ' If there are rows that still need to be hidden then hide those rows
End If
'
If Not FullRangeOfRowsToBeUnhidden Is Nothing Then ' If there are rows that should be visible then ...
For Each RangeRow In FullRangeOfRowsToBeUnhidden.Rows ' Loop through each row in FullRangeOfRowsToBeUnhidden
If RangeRow.EntireRow.Hidden Then ' If the row is currently hidden then ...
If Not RemainingRangeOfRowsToBeUnhidden Is Nothing Then ' If we have already saved a row number to RemainingRangeOfRowsToBeUnhidden then ...
Set RemainingRangeOfRowsToBeUnhidden = _
Union(RemainingRangeOfRowsToBeUnhidden, RangeRow) ' Add the RangeRow to the rows already in RemainingRangeOfRowsToBeUnhidden
Else ' Else ...
Set RemainingRangeOfRowsToBeUnhidden = RangeRow ' Save the RangeRow to RemainingRangeOfRowsToBeUnhidden
End If
End If
Next ' Loop back
'
If Not RemainingRangeOfRowsToBeUnhidden Is Nothing Then _
RemainingRangeOfRowsToBeUnhidden.EntireRow.Hidden = False ' If we have saved atleast 1 row number to RemainingRangeOfRowsToBeUnhidden then ...
' ' unhide those rows
End If
'
Application.Calculation = xlCalculationAutomatic ' Turn calculation mode back on
Application.ScreenUpdating = True ' Turn ScreenUpdating back on
End Sub
