Fix Worksheet_Change VBA Errors When Clearing Multiple Table Rows in WPS Spreadsheets
Question details
The user needs to handle the Worksheet_Change event in WPS Spreadsheets when Target contains multiple cells after clearing or pasting table rows, avoiding type-mismatch errors.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A VBA macro uses the Worksheet_Change event to update adjacent cells when the Sign column in a table changes; users may clear or edit multiple table rows at once.
- Observed behavior
- When Target refers to multiple cells, treating it as a single cell can cause a type-mismatch error or incorrect updates.
Open the workbook in WPS Spreadsheets, enable the Developer tab, and confirm the table name and Sign column match the code before testing the macro.
Process the multi-cell Target with Intersect and a loop
Use this solution when the Worksheet_Change event must handle multi-cell changes from clearing, pasting, or filling several table rows without type-mismatch errors.
Worksheet_Change receives the entire edited range as Target. When multiple rows are cleared or pasted, Target is not a single cell, so a single-cell comparison can fail or update only one area.
Using Intersect limits processing to the table column you actually monitor, and a For Each loop safely handles each affected cell while Application.EnableEvents prevents recursive events.
In WPS Spreadsheets, click the Developer tab, then select Visual Basic to open the VBA editor.
In the Project Explorer pane, double-click the worksheet module that contains the table you want to monitor.
Paste the following code into the worksheet module, adjusting "Table1[Sign]" if your table or column name differs: Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range, affected As Range Set affected = Intersect(Me.Range("Table1[Sign]"), Target) If affected Is Nothing Then Exit Sub On Error GoTo CleanUp Application.EnableEvents = False For Each cell In affected If cell.Value = "" Then cell.Offset(0, -1).ClearContents cell.Offset(0, -2).ClearContents Else cell.Offset(0, -1).Value = "RS" cell.Offset(0, -2).Value = Date End If Next cell CleanUp: Application.EnableEvents = True End Sub
Return to the sheet and clear several rows in the Sign column to confirm adjacent cells update correctly and no type-mismatch error appears.
Fix Worksheet_Change Multi-Cell Errors in WPS Spreadsheets VBA
Open the WPS Spreadsheets VBA editor, replace the Worksheet_Change handler with a loop that checks Intersect against the Sign column, and safely restores events after each batch update.
- 1. Enable the Developer tab: In WPS Spreadsheets, click File > Options > Customize Ribbon and enable Developer, or use the existing Developer tab.
- 2. Open the VBA editor: Click Developer > Visual Basic to open the editor, then double-click the worksheet module for the table.
- 3. Replace the Worksheet_Change code: Delete the existing Worksheet_Change sub and paste the Intersect-and-loop version, updating the table name and column header.
- 4. Run a multi-row test: Return to the sheet and clear several rows in the Sign column to confirm adjacent cells update and no error appears.
- 5. Save as macro-enabled workbook: Save the workbook as a macro-enabled .xlsm file so the VBA fix is retained.
Frequently Asked Questions
Why does Worksheet_Change fire with a multi-cell Target when I clear several table rows?
WPS Spreadsheets passes the entire cleared or pasted range as Target. Use Intersect to narrow the range to the monitored table column before reading cell values.
How do I stop Worksheet_Change from running again after my code updates cells?
Set Application.EnableEvents = False before making changes, and use an On Error GoTo CleanUp label to restore Application.EnableEvents = True even if an error occurs.
What should I do if my table column is not named Sign?
In the VBA editor, replace Table1[Sign] with your table name and column header, for example Me.Range("SalesTable[Status]").
Can I use this Worksheet_Change fix in Microsoft Excel too?
Yes. The Intersect loop and EnableEvents logic are compatible with Excel VBA, and WPS Spreadsheets can open and save macro-enabled .xlsm workbooks.
Why does my code clear the wrong cells after pasting multiple rows?
The Offset values must match the columns adjacent to the Sign column. Check that Offset(0, -1) and Offset(0, -2) point to the intended date and status cells, or adjust them to match your table layout.
