Forum Discussion

Knimmo's avatar
Knimmo
Copper Contributor
Oct 17, 2023
Solved

Hiding Rows with VBA based on a dropdown List

Hi,   I am looking for a VBA code to hide rows that are greater than a value in a dropdown list. For example, in the below image, since the drop down shows 3, i would like the rows for 4-10 to be h...
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Oct 17, 2023

    Knimmo 


    Right-click the sheet tab.
    Select 'View Code' from the context menu.
    Copy the code listed below into the code window.
    Close the Visual Basic Editor.
    Save the workbook as a macro-enabled workbook (*.xlsm).
    Make sure that you allow macros when you open it.
    Code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim n As Long
    Dim m As Long
    If Not Intersect(Range("C4"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        m = Range("B:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        n = Val(Range("C4").Value)
        Range("A7:A" & m).EntireRow.Hidden = False
        If n >= 0 And n < m - 7 Then
            Range("A" & n + 7 & ":A" & m).EntireRow.Hidden = True
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub
HansVogelaar's avatar
HansVogelaar
MVP
Oct 17, 2023

Knimmo 


Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the code window.
Close the Visual Basic Editor.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.
Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim n As Long
    Dim m As Long
    If Not Intersect(Range("C4"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        m = Range("B:B").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        n = Val(Range("C4").Value)
        Range("A7:A" & m).EntireRow.Hidden = False
        If n >= 0 And n < m - 7 Then
            Range("A" & n + 7 & ":A" & m).EntireRow.Hidden = True
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub
Knimmo's avatar
Knimmo
Copper Contributor
Oct 18, 2023

HansVogelaar follow up question - i am now trying to hide rows based on the value in column A (i've added in page numbers in column A and i want to hide all rows with a page number greater than the value in Cell P3. I have tried to use the same code you provided for the previous answer and modify it but am running into issues. Any ideas?

 

 

 

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 18, 2023

    Knimmo 

    Here is a modified version:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim n As Long
    Dim rng As Range
    If Not Intersect(Range("P3"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Cells.EntireRow.Hidden = False
        n = Val(Range("P3").Value)
        If n > 0 Then
            Set rng = Range("A:A").Find(What:=n + 1, LookAt:=xlWhole)
            If Not rng Is Nothing Then
                Range(rng, Range("A" & Rows.Count)).EntireRow.Hidden = True
            End If
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Resources