A VBA code that prompts the user how many rows he/she wants in an Excel file, then showing only such quantity of rows

Question

is there a way to ask the user how many rows he/she needs in an Excel file? And then with that information the worksheet will only show the required quantity of rows and hide every other cell, just to avoid the user to edit such cells.

As an example: It prompts how many rows I want, I type 3 rows and the excel worksheet will only have those 3 rows showing, the rest will be hidden automatically.

Thanks for your help.

Answer 1

I never mentioned that it was impossible, I just said that it would be counter intuitive. Try this and let me know if this works.

Public Sub HideRows()
    Dim RowsToKeep As Long
    RowsToKeep = CLng(InputBox("How many rows should I keep?") + 1)

    With ActiveSheet
        .Cells.EntireRow.Hidden = False
        .Rows(RowsToKeep & ":" & .Rows.Count).EntireRow.Hidden = True
    End With

End Sub

---

edit: Answer updated to reflect the response to comment

1. You must keep this code in the module.
2. Click Insert > Module.
3. Paste the code there.
4. In ThisWorkbook module, there are 2 dropdowns at the top.
5. Select first dropdown and select workbook.
6. 2nd dropdown will automatically,
7. select 'Open" and it will autogenerate a code for Workbook_open.
8. Just add 'Call HideRows' without quotes