Filter a row based on cell value

%3CLINGO-SUB%20id%3D%22lingo-sub-3129157%22%20slang%3D%22en-US%22%3EFilter%20a%20row%20based%20on%20cell%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3129157%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20wondering%20if%20it%20was%20possible%20to%20show%20only%20a%20specific%20amount%20of%20rows%20based%20on%20a%20single%20value%20in%20a%20cell.%20Down%20here%20an%20example.%20If%20amount%20%3D%2016%2C%20to%20only%20show%200-16.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20look%20forward%20to%20an%20answer!%3C%2FP%3E%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Schermafbeelding%202022-02-07%20155111.png%22%20style%3D%22width%3A%20653px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345857i3BC6A489A51DD135%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Schermafbeelding%202022-02-07%20155111.png%22%20alt%3D%22Schermafbeelding%202022-02-07%20155111.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3129157%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3129240%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20a%20row%20based%20on%20cell%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3129240%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1299505%22%20target%3D%22_blank%22%3E%40Sjoerd310%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERight-click%20the%20sheet%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20View%20Code%20from%20the%20context%20menu.%3C%2FP%3E%0A%3CP%3ECopy%20the%20following%20code%20into%20the%20worksheet%20module.%20I%20have%20assumed%20that%20number%20of%20rows%20to%20be%20shown%20is%20in%20cell%20B1.%20Modify%20the%20code%20if%20you%20use%20another%20cell.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20Dim%20n%20As%20Long%0A%20%20%20%20If%20Not%20Intersect(Range(%22B1%22)%2C%20Target)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20n%20%3D%20Val(Range(%22B1%22).Value)%0A%20%20%20%20%20%20%20%20If%20n%20%26gt%3B%200%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Range(%22A1%3AA%22%20%26amp%3B%20n).EntireRow.Hidden%20%3D%20False%0A%20%20%20%20%20%20%20%20%20%20%20%20Range(%22A%22%20%26amp%3B%20n%20%2B%201%20%26amp%3B%20%22%3AA%22%20%26amp%3B%20Rows.Count).EntireRow.Hidden%20%3D%20True%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ESwitch%20back%20to%20Excel.%3C%2FP%3E%0A%3CP%3ESave%20the%20workbook%20as%20a%20macro-enabled%20workbook%20(.xlsm).%3C%2FP%3E%0A%3CP%3EMake%20sure%20that%20you%20allow%20macros%20when%20you%20open%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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!

 Schermafbeelding 2022-02-07 155111.png

1 Reply

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