Optimize VBA to hide rows and rows below based on task-level and task status

ht55cd3

New Member
Joined
Aug 30, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
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.

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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Forum statistics

Threads
1,214,918
Messages
6,122,252
Members
449,075
Latest member
staticfluids

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
Back
Top