-
Notifications
You must be signed in to change notification settings - Fork 0
/
FilteringColumnsORCondition.bas
36 lines (34 loc) · 1.22 KB
/
FilteringColumnsORCondition.bas
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Sub TN_GL_hold()
'
' updated Macro
'
Dim hideRng As Range
Dim i As Long
Dim lastrow As Long
' Sheet2 => define the sheet you are using
With Sheet2
' sheet2 = TNs
'count how many rows are using column B
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
'Unhide rows
.Cells.EntireRow.Hidden = False
'Lets say my table starts by 6 and ends in lastrow value
For i = 6 To lastrow
'Condition in this case if columns W OR Y OR AA for all rows check if the value is not "on Hold"
If Not (.Range("W" & i) = "On hold" Or .Range("Y" & i) = "On Hold" Or .Range("AA" & i) = "On hold") Then
'if hide range is nohting then set hide range => first record
If hideRng Is Nothing Then
Set hideRng = .Range("B" & i) ' first record not on hold
Else
' else append range to first record
Set hideRng = Union(.Range("B" & i), hideRng) ' append records not on hold
End If
End If
Next
' if there are records in hide range then hide them
If Not hideRng Is Nothing Then hideRng.EntireRow.Hidden = True
' and that's it, you have filtered all rows without a ON hold value in columns W or Y or AA
On Error Resume Next
On Error GoTo 0
End With
End Sub