Filter a row based on cell value

Occasional Visitor



I was wondering if it was possible to show only a specific amount of rows based on a single value in a cell. Down here an example. If amount = 16, to only show 0-16.


I look forward to an answer!

 Schermafbeelding 2022-02-07 155111.png

1 Reply


Right-click the sheet tab.

Select View Code from the context menu.

Copy the following code into the worksheet module. I have assumed that number of rows to be shown is in cell B1. Modify the code if you use another cell.


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim n As Long
    If Not Intersect(Range("B1"), Target) Is Nothing Then
        n = Val(Range("B1").Value)
        If n >= 0 Then
            Range("A1:A" & n + 2).EntireRow.Hidden = False
            Range("A" & n + 3 & ":A" & Rows.Count).EntireRow.Hidden = True
        End If
    End If
End Sub


Switch back to Excel.

Save the workbook as a macro-enabled workbook (.xlsm).

Make sure that you allow macros when you open it.