Run an Excel VBA Macro on Sheets with Different Row Counts
Question details
Make an Excel VBA macro work with worksheets that have different numbers of data rows.

- 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.
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.
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.
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.
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").
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
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.

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.

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.




