Streamlining VBA to hide/unhide rows

Anonymous
2016-06-26T03:36:31+00:00

Hi, I have 4 worksheet change events and the idea for each is that, if the value in column BX for a row within the applicable range is "Hide", then that row should be hidden (otherwise unhidden).  The below code works, but is slow/inefficient so hoping to get help with making it work better.

Thank you!

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Not Intersect(Range("CGLPolicyYears"), Target) Is Nothing Then

        Application.EnableEvents = False

        Application.ScreenUpdating = False

        ActiveSheet.Unprotect Password:="incomplete"

        Rows("25:31").EntireRow.Hidden = True

        beginRow = 25

        endRow = 31

        chkCol = 76

        For rowCnt = beginRow To endRow

            If Cells(rowCnt, chkCol).Value = "Hide" Then

            Cells(rowCnt, chkCol).EntireRow.Hidden = True

            Else

            Cells(rowCnt, chkCol).EntireRow.Hidden = False

        End If

        Next rowCnt

        ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, AllowFormattingRows:=True, Password:="incomplete"

        Application.ScreenUpdating = True

        Application.EnableEvents = True

    End If

    If Not Intersect(Range("ALPolicyYears"), Target) Is Nothing Then

        Application.EnableEvents = False

        Application.ScreenUpdating = False

        ActiveSheet.Unprotect Password:="incomplete"

        Rows("408:414").EntireRow.Hidden = True

        beginRow = 408

        endRow = 414

        chkCol = 76

        For rowCnt = beginRow To endRow

            If Cells(rowCnt, chkCol).Value = "Hide" Then

            Cells(rowCnt, chkCol).EntireRow.Hidden = True

            Else

            Cells(rowCnt, chkCol).EntireRow.Hidden = False

        End If

        Next rowCnt

        ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, AllowFormattingRows:=True, Password:="incomplete"

        Application.ScreenUpdating = True

        Application.EnableEvents = True

    End If

    If Not Intersect(Range("CGLLargeLosses"), Target) Is Nothing Then

        Application.EnableEvents = False

        Application.ScreenUpdating = False

        ActiveSheet.Unprotect Password:="incomplete"

        Rows("62:168").EntireRow.Hidden = True

        beginRow = 62

        endRow = 168

        chkCol = 76

        For rowCnt = beginRow To endRow

            If Cells(rowCnt, chkCol).Value = "Hide" Then

            Cells(rowCnt, chkCol).EntireRow.Hidden = True

            Else

            Cells(rowCnt, chkCol).EntireRow.Hidden = False

        End If

        Next rowCnt

        ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, AllowFormattingRows:=True, Password:="incomplete"

        Application.ScreenUpdating = True

        Application.EnableEvents = True

    End If

    If Not Intersect(Range("ALLargeLosses"), Target) Is Nothing Then

        Application.EnableEvents = False

        Application.ScreenUpdating = False

        ActiveSheet.Unprotect Password:="incomplete"

        Rows("445:551").EntireRow.Hidden = True

        beginRow = 445

        endRow = 551

        chkCol = 76

        For rowCnt = beginRow To endRow

            If Cells(rowCnt, chkCol).Value = "Hide" Then

            Cells(rowCnt, chkCol).EntireRow.Hidden = True

            Else

            Cells(rowCnt, chkCol).EntireRow.Hidden = False

        End If

        Next rowCnt

        ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, AllowFormattingRows:=True, Password:="incomplete"

        Application.ScreenUpdating = True

        Application.EnableEvents = True

    End If

End Sub

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

2 answers

Sort by: Most helpful
  1. Anonymous
    2016-06-26T07:54:25+00:00

    Perfect - thank you!

    Was this answer helpful?

    0 comments No comments
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.


    Comments have been turned off. Learn more