Forum Discussion

Sjoerd310's avatar
Sjoerd310
Copper Contributor
Feb 07, 2022

Filter a row based on cell value

Hello,

 

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!

 

excel
Formulas and Functions

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 07, 2022

    Sjoerd310 

    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.

Resources