Excel VBA Input Box to Select a Row by Number

To select a worksheet row by its number, use VBA’s Application.InputBox with Type:=1, validate the entered number, then select ws.Rows(rowNumber). If you want the user to choose a row by clicking a cell instead, use Type:=8 and select that cell’s entire row.

Choose How the User Will Identify the Row

There are two useful meanings of “select a row with an input box.” A number prompt asks the user to enter a worksheet row, such as 12. A range prompt lets the user point to a cell on the desired row. Both approaches can select the whole worksheet row, but they suit different tasks.

Ask for a row number when the user knows the row number, or when a procedure needs a predictable numeric input.
Ask the user to select a cell when row numbers are not obvious, or when the user can identify the row from its contents.
Use a custom UserForm when the prompt needs labels, a list of matching records, or more involved validation.

The examples below target a worksheet named Data. Change that name to the actual sheet tab name in your workbook. Using a named worksheet is safer than relying on whichever sheet happens to be active.

Prompt for a Row Number

This macro displays a numeric input box, checks that the answer is a whole number within the worksheet’s row limits, and selects that row. In modern Excel worksheets, the maximum is 1,048,576 rows; the code checks the worksheet’s own row count rather than hard-coding that limit.

Sub SelectRowByNumber()
    Dim ws As Worksheet
    Dim answer As Variant
    Dim rowNumber As Long

    ' Use the sheet that contains the rows to select.
    Set ws = ThisWorkbook.Worksheets("Data")

    answer = Application.InputBox( _
        Prompt:="Enter the worksheet row number to select:", _
        Title:="Select a row", _
        Type:=1)

    ' Cancel returns Boolean False; do not treat it as row zero.
    If VarType(answer) = vbBoolean Then Exit Sub

    If answer <> Fix(answer) Then
        MsgBox "Enter a whole-number row number.", vbExclamation
        Exit Sub
    End If

    If answer < 1 Or answer > ws.Rows.Count Then
        MsgBox "Enter a row from 1 to " & ws.Rows.Count & ".", vbExclamation
        Exit Sub
    End If

    rowNumber = CLng(answer)
    ws.Activate
    ws.Rows(rowNumber).Select
End Sub

Here, Type:=1 restricts the Application.InputBox response to a number. The check for vbBoolean matters because clicking Cancel returns Boolean False; it is not the same as entering the number 0. The macro rejects decimals and values outside the sheet’s valid row range before attempting to select anything.

Before running a macro, save a copy of the workbook: VBA actions cannot be reversed with Ctrl+Z. To add this code on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the procedure into the module. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm).

Let the User Click a Cell in the Row

If the user should choose a row visually, an Application.InputBox with Type:=8 lets them select a cell or range on a worksheet. The following code uses the first cell in the selection and selects that cell’s entire worksheet row.

Sub SelectRowByCell()
    Dim picked As Range

    ' Type 8 returns a Range; Cancel can cause an assignment error.
    On Error Resume Next
    Set picked = Application.InputBox( _
        Prompt:="Select a cell in the row you want, then click OK:", _
        Title:="Choose a row", _
        Type:=8)
    On Error GoTo 0

    If picked Is Nothing Then Exit Sub

    picked.Worksheet.Activate
    picked.Cells(1, 1).EntireRow.Select
End Sub

The Set keyword is required because the returned value is a Range object. The brief error-handling section allows the macro to handle Cancel without stopping on an assignment error; the following Is Nothing check then exits if no range was chosen. If the user selects several cells or rows, this example uses only the first cell’s row. To require a single-cell choice, add a check such as If picked.Cells.CountLarge > 1 Then and display an instruction before exiting.

How Application.InputBox Works

Excel has both VBA’s built-in InputBox function and Excel’s Application.InputBox method. The built-in function returns text, even when the user types digits. Application.InputBox offers a Type argument that can request different kinds of input, including a number with Type:=1 or a range reference with Type:=8. That distinction is why the examples use Application.InputBox.

The input box itself does not select a worksheet row. It gathers the user’s choice; the VBA statements that follow interpret the response and make the selection. For a numeric response, the code converts the validated value to a Long row number. For a range response, it works with the returned Range object and uses EntireRow.

In either case, selection is an interface action, not a data lookup. It highlights a row but does not automatically find a matching name, filter records, or move data. If the request is really “find the row containing this customer,” the macro needs a separate search step.

Validate the Row and Protect Against Mistakes

Input validation prevents ordinary mistakes from becoming confusing runtime errors. A row number must be at least 1, no greater than the worksheet’s row count, and an integer. The numeric example checks all three conditions. If you instead use the built-in text-returning InputBox, check that the response is numeric and integral before converting it; do not assume every string that looks like a number is a valid row.

Consider what “row 12” means in the workbook. It always means worksheet row 12, not the twelfth visible record, the twelfth row after a header, or the twelfth row in a filtered list. If row 1 contains headers, the macro will still allow row 1. You can disallow it with an additional rule, or ask for a record number and calculate the corresponding worksheet row separately.

When a macro will be used by others, helpful validation messages should say what kind of value is expected and which range is allowed. Avoid silently changing an invalid entry—for example, rounding 12.7 to 13—because the user may not realize that a different row was selected.

Selecting a Whole Row Versus a Data Range

ws.Rows(rowNumber).Select selects the entire worksheet row, including cells far beyond the columns used by the table. That is appropriate when the goal is to highlight a row or apply an action to the whole sheet row. It can be inconvenient when copying, formatting, or processing only a table’s columns.

If the table occupies columns A through F and the desired row is 12, select only that portion with ws.Range("A" & rowNumber & ":F" & rowNumber).Select. This still selects cells from the chosen worksheet row, but avoids including every other column. For a table that can grow or move, reference the Excel Table (ListObject) rather than fixing the boundaries to A:F.

A filtered list needs extra care: selecting worksheet row 12 does not mean “select the twelfth visible record.” Hidden or filtered-out rows retain their worksheet numbers. If the task is to choose among visible records, use a method that explicitly works with the filtered range or presents the records for selection.

Common Problems and Their Causes

The wrong sheet becomes active: code using ActiveSheet.Rows(...) depends on the current selection. Set a worksheet variable explicitly, activate that worksheet, and select from it.
Cancel causes an error: a numeric Application.InputBox returns Boolean False on Cancel, while a Type 8 range assignment may raise an error. Handle each return type as shown rather than assuming the user always clicks OK.
Excel says the Select method failed: selection generally requires the target worksheet to be active. The examples activate the relevant sheet first. Protection settings or workbook state can also prevent the intended selection.
The macro cannot find the worksheet: ThisWorkbook.Worksheets("Data") requires an exact sheet tab name. Rename the reference in the code if the tab has a different name.
The macro does not run: confirm that the file is saved as .xlsm and that macros are permitted under the organization’s security settings. Do not enable macros in a workbook from an untrusted source.

Selection is often unnecessary inside a macro. If the next step is to read or format cells, directly refer to the worksheet and range instead of selecting them. Direct references are less dependent on the active sheet and make code easier to maintain.

Windows, Mac, and Excel for the Web

The VBA examples are intended for desktop Excel. On Windows, Alt+F11 opens the Visual Basic Editor and Alt+F8 opens the Macro dialog. On a Mac, the function-key shortcut can depend on the keyboard settings; Option+F11 or Fn+Option+F11 commonly opens the editor, and Option+F8 may open the Macro dialog. You can also use the Developer tab to access VBA tools. If it is hidden on Windows, enable it under File > Options > Customize Ribbon; on Mac, use Excel > Settings > Ribbon & Toolbar.

Excel for the web can open macro-enabled workbooks, but it cannot create, edit, or run VBA macros. Use the desktop application for these procedures. Excel for Mac supports VBA, although some features and keyboard shortcuts differ from Windows; test a macro on a copy when sharing a workbook across platforms.

Frequently Asked Questions

Can an Excel input box select a row directly?
Not by itself. It collects a row number or range reference; VBA then uses that response with Rows(...).Select or a range’s EntireRow property.

How do I select a row based on a value in a cell?
Search the relevant column for the value, identify the matching cell, and select its row. An input box can collect the search text, but the macro needs a lookup or search operation in addition to the selection step.

How can I select the row the user clicks?
Use Application.InputBox with Type:=8, assign its result to a Range variable with Set, and select the chosen cell’s EntireRow. Include handling for Cancel.

Does selecting a row select only visible rows?
No. Rows(rowNumber).Select refers to the worksheet row number, whether or not that row is hidden by a filter. A visible-record selection requires code that accounts for the filtered range.

Can I undo a VBA row selection with Ctrl+Z?
Excel’s Undo command does not undo VBA macro actions. Selecting a row does not usually alter cell values, but save a copy before running macros that may also edit or delete data.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.