SOLVED

Hiding Rows with VBA based on a dropdown List

Copper Contributor

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 hidden. 

 

Knimmo_1-1697560127878.png

 

 

 

5 Replies
best response confirmed by Knimmo (Copper Contributor)
Solution

@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 

Instead of hiding rows, you could leave the source data untouched and use a formula to select the first n rows.

= TAKE(table, numberUnits)
This worked great. You are a life saver!

@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?

 

 

Knimmo_0-1697635473664.png

 

@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
1 best response

Accepted Solutions
best response confirmed by Knimmo (Copper Contributor)
Solution

@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

View solution in original post