Forum Discussion
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 hidden.
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
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- 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?
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
Instead of hiding rows, you could leave the source data untouched and use a formula to select the first n rows.
= TAKE(table, numberUnits)
