Fix VBA Code for Selecting the Second-to-Last Used Row in WPS Office Excel
Question details
The user needs to correct VBA code in WPS Office Spreadsheets that tries to select from row 8 through the second-to-last used row in column C, but incorrectly assigns the row number to a Range variable and places the variable name inside a string.
- Product
- WPS Office Spreadsheets (Excel)
- Device & OS
- not provided
- Scenario
- While writing a VBA macro, the user wants to select the entire rows from row 8 through the second-to-last used entry in column C.
- Observed behavior
- The current code assigns the calculated row number to a Range variable and includes the variable name inside quotation marks, so the macro does not select the intended range or returns a type mismatch.
Open the workbook in WPS Office Spreadsheets and press Alt+F11 to open the VBA editor. Check which worksheet contains the data in column C so you can qualify the Cells and Rows references with that sheet.
Use a Long Row Number Variable with Correct Range Concatenation
Use this when the macro must select all rows from row 8 through the second-to-last used row in column C, and you need reliable, readable VBA code.
The error occurs because the second-to-last row is stored in a Range or String variable, or the variable name is placed inside quotes, so VBA treats it as literal text instead of a row number.
In WPS Office Spreadsheets, press Alt+F11 to open the Microsoft Visual Basic for Applications editor and locate the module containing the macro.
At the top of the subroutine, declare Dim lastRow As Long and then assign it with lastRow = Cells(Rows.Count, "C").End(xlUp).Row - 1.
Use Range("C1:C" & lastRow).Select to select column C from C1 through the second-to-last used row, or use Rows("8:" & lastRow).Select to select entire rows from row 8 through that row.
Change Cells and Rows to a worksheet object, for example Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1"): lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row - 1: ws.Rows("8:" & lastRow).Select.
Select the Second-to-Last Used Row with Fully Qualified Worksheet References
Use this if the macro runs from a module and you need to avoid selecting the wrong sheet or getting type mismatches.
Fix VBA Row Selection in WPS Office Spreadsheets with a Long Row Variable
Use WPS Office Spreadsheets' VBA editor to store the second-to-last row in a Long variable and build the range reference with & concatenation, so your macro selects the intended rows reliably.
- 1. Open WPS Office Spreadsheets and the VBA editor: Launch WPS Office, open your macro-enabled workbook, and press Alt+F11 to open the VBA editor.
- 2. Insert or edit the macro module: Locate the module containing the selection macro, or go to Insert > Module to create a new one.
- 3. Add the corrected VBA code: Paste the corrected code, for example: Dim lastRow As Long: lastRow = Cells(Rows.Count, "C").End(xlUp).Row - 1: Rows("8:" & lastRow).Select.
- 4. Run or save the macro: Press F5 to run the macro and confirm it selects rows 8 through the second-to-last used row in column C, then save the workbook as a macro-enabled file.
Frequently Asked Questions
Why does my VBA code select the wrong range when I put LastSecondC inside quotes?
When LastSecondC is inside quotation marks, VBA treats it as literal text instead of a variable. Use Rows("8:" & LastSecondC).Select so VBA reads the actual row number.
How do I get the second-to-last used row in column C?
Declare a Long variable and assign it with lastRow = Cells(Rows.Count, "C").End(xlUp).Row - 1. This finds the last used row in column C and subtracts 1.
Why do I get a type mismatch with this VBA range selection?
A type mismatch usually occurs when the row number is assigned to a Range variable or when a string is passed where a number is expected. Use Dim lastRow As Long and concatenate it with & in the Rows or Range reference.
How do I stop the macro from selecting the wrong worksheet?
Qualify the worksheet references in VBA. Set Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1") and then use ws.Cells, ws.Rows, and ws.Range so the macro works on the target sheet.
Can I select entire rows instead of only column C?
Yes. Use Rows("8:" & lastRow).Select to select all cells in rows 8 through the second-to-last used row in column C.
