I have a management excel file with the task at column A, task-level at column B, and task status at column C.
I wrote a code to work with these rules:
- If task status is "Not Yet", "Done", "Delay" then the task's row and all rows with larger task levels below it will hide.
- Only apply for level ≥ 4
I posted a shortened version of my file to help you understand my idea, my actual file has more columns, formulas... so it takes about 5-8 mins when I run the code.
So I would need some help in optimizing my code or a better solution for my file.
Test.xlsm
In case you don't want to download the file, this is my code.
I wrote a code to work with these rules:
- If task status is "Not Yet", "Done", "Delay" then the task's row and all rows with larger task levels below it will hide.
- Only apply for level ≥ 4
I posted a shortened version of my file to help you understand my idea, my actual file has more columns, formulas... so it takes about 5-8 mins when I run the code.
So I would need some help in optimizing my code or a better solution for my file.
Test.xlsm
In case you don't want to download the file, this is my code.
VBA Code:
Sub HideRow()
Rows.EntireRow.Hidden = False
Dim SL, xRow, xCol As Long
SL = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
xCol = Application.InputBox(Prompt:="Select any cell in Status Column", Title:="Hide Rows when meet conditions", Type:=8).Column
Application.ScreenUpdating = False
xRow = 1
Cells(xRow, 2).Select
Do While xRow <= SL
Dim Level
Level = Cells(xRow, 2).Value
Dim Status As String
Status = Cells(xRow, xCol).Value
Cells(xRow, 2).Select
Select Case Level
Case 3
If Status = "Not Yet" Or Status = "Delay" Or Status = "Done" Then ActiveCell.EntireRow.Hidden = True
Do Until ActiveCell.Offset(1, 0).Value <= 3
If ActiveCell.Offset(1, 0).Value > 3 And Status = "Not Yet" Or Status = "Delay" Or Status = "Done" Then ActiveCell.Offset(1, 0).EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Select
Loop
Case 4
If Status = "Not Yet" Or Status = "Delay" Or Status = "Done" Then ActiveCell.EntireRow.Hidden = True
Do Until ActiveCell.Offset(1, 0).Value <= 4
If ActiveCell.Offset(1, 0).Value > 4 And Status = "Not Yet" Or Status = "Delay" Or Status = "Done" Then ActiveCell.Offset(1, 0).EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Select
Loop
Case 5
If Status = "Not Yet" Or Status = "Delay" Or Status = "Done" Then ActiveCell.EntireRow.Hidden = True
Do Until ActiveCell.Offset(1, 0).Value <= 5
If ActiveCell.Offset(1, 0).Value > 5 And Status = "Not Yet" Or Status = "Delay" Or Status = "Done" Then ActiveCell.Offset(1, 0).EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Select
Loop
Case 6
If Status = "Not Yet" Or Status = "Delay" Or Status = "Done" Then ActiveCell.EntireRow.Hidden = True
Do Until ActiveCell.Offset(1, 0).Value <= 6
If ActiveCell.Offset(1, 0).Value > 6 And Status = "Not Yet" Or Status = "Delay" Or Status = "Done" Then ActiveCell.Offset(1, 0).EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Select
Loop
Case 7
If Status = "Not Yet" Or Status = "Delay" Or Status = "Done" Then ActiveCell.EntireRow.Hidden = True
Do Until ActiveCell.Offset(1, 0).Value <= 7
If ActiveCell.Offset(1, 0).Value = 7 And Status = "Not Yet" Or Status = "Delay" Or Status = "Done" Then ActiveCell.Offset(1, 0).EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Select
Loop
xRow = ActiveCell.Row
End Select
xRow = xRow + 1
Loop
Application.ScreenUpdating = True
End Sub