VBA & Macro Problems

Run an Excel VBA Macro on Sheets with Different Row Counts

John WilsonJohn Wilson Nov 18, 2026 869 views

Question details

Make an Excel VBA macro work with worksheets that have different numbers of data rows.

Run an Excel VBA Macro on Sheets with Different Row Counts
Product
Microsoft Excel
Device & OS
not provided
Scenario
A recorded macro needs to process data on sheets whose row counts vary.
Observed behavior
The macro may rely on fixed worksheet references or row limits, and Run-time Error 9 can occur when a referenced workbook or worksheet cannot be found.
Before you start

Open the workbook and identify the sheet to process, the column that reliably contains data, and the first data row. Locate the macro in the VBA editor so you can check its workbook, worksheet, and range references.

Solution 1Recommended

Check VBA references and find the last data row dynamically

Use this approach when a macro references a missing sheet or assumes that every sheet has the same number of rows.

Run-time Error 9 usually means a workbook, worksheet, or array index is out of range. A fixed row limit can also produce incomplete results, but it does not necessarily cause Error 9.

The example assumes row 1 contains headers and column A identifies the data rows. It prints values to the Immediate window rather than changing the worksheet.

1
Inspect the failing reference

Open Developer > Visual Basic, run the macro, and select Debug if the error appears. Check the highlighted line for a workbook name, worksheet name, or array index that does not exist.

2
Identify the correct workbook and sheet

In the VBA editor, check the sheet name in the workbook's sheet tabs and match it exactly in the code. Use ThisWorkbook when the macro is stored in the target workbook; if it is stored elsewhere, reference the open target workbook explicitly with Workbooks("YourFile.xlsm").

3
Determine the last row

In a standard VBA module, adapt this example to your sheet name and data column: Sub ProcessVariableRows() Dim ws As Worksheet Dim lastRow As Long Dim i As Long On Error Resume Next Set ws = ThisWorkbook.Worksheets("Sheet1") On Error GoTo 0 If ws Is Nothing Then MsgBox "The specified sheet was not found." Exit Sub End If lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row If lastRow < 2 Then Exit Sub For i = 2 To lastRow Debug.Print ws.Cells(i, "A").Value Next i End Sub

4
Adapt and test the macro

Replace "Sheet1" and column A with the appropriate sheet and key column, then incorporate your processing code inside the For loop. Open View > Immediate Window to inspect the example's output, and run the macro on sheets with different row counts.

Check VBA references and find the last data row dynamically
For a precise correction: The original macro and the line highlighted by Debug are needed to identify the exact cause. The last-row method depends on the chosen column containing an entry on the final data row.
Free Microsoft Office alternative

Work with Variable-Size Spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility, making it easy to move everyday workbook tasks. It does not fix errors in Excel itself, and VBA macro support can vary by WPS edition.

Review and edit spreadsheets with different row counts in WPS SpreadsheetsOpen and save common Microsoft Excel formats, including XLSXUse a familiar interface for a smoother transition from Microsoft OfficeWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

What causes Run-time Error 9 in an Excel macro?

It generally means a workbook, worksheet, or array index is outside the available collection. Select Debug when the error appears and inspect the highlighted reference.

How do I find the last used row in a specific column?

In VBA, use ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, replacing A with a column that has an entry on the last data row.

What is the difference between ThisWorkbook and the active workbook?

ThisWorkbook refers to the workbook containing the VBA code. ActiveWorkbook refers to the workbook currently active in Excel, which may be different.

Will the sample macro process my data automatically?

No. It checks for the named sheet and prints column A values to the Immediate window. Add your own processing code inside the For loop after checking the sheet, column, and starting row.

Can WPS Office fix an Excel VBA error?

WPS Office cannot correct an error in Microsoft Excel itself. Its VBA support can vary by edition, so check macro compatibility before moving a macro-enabled workbook.