VBA & Macro Problems

Fix Excel VBA Delete Rows Between a Fixed Start and Dynamic End in WPS Office

Camila Milosovich Nov 13, 2026 869 views

Question details

The user needs to correct an Excel VBA procedure that copies a template, finds a starting and ending month, and deletes rows outside the required date range without causing WorksheetFunction.Match to fail after row positions shift.

Fix Excel VBA Delete Rows Between a Fixed Start and Dynamic End

Product: WPS Office Excel / Excel VBA
Device & OS: not provided
Scenario: A VBA macro duplicates a template, identifies a fixed starting row and a dynamic ending month row, and then removes rows outside that range.
Observed behavior: WorksheetFunction.Match fails or deletes the wrong row when earlier row deletions shift the positions of the remaining dates.

Before you start

Before editing the macro, check where your VBA code calls WorksheetFunction.Match relative to row deletions, and confirm the exact fixed start row and how RowEnd is calculated.

Solution 1Recommended

Delete Rows with a Stored Row Range Instead of Selecting Rows

Use this solution when the ending row has already been calculated before deletion and you want to remove the target block safely.

Deleting rows shifts all rows below the deletion point upward. If your code deletes earlier rows before calculating or using later row numbers, the stored match positions may no longer point to the correct dates.

Open the VBA editor

In WPS Office Excel, press Alt+F11 to open the Microsoft Visual Basic for Applications editor.

Locate the deletion section

In the Project Explorer, open the module that contains the Create New User Tab macro or your row deletion procedure.

Replace the Delete command with a direct range

Use a line such as WSN.Range("A11:A" & RowEnd - 1).EntireRow.Delete without calling Select or Activate first.

Run the macro and verify the range

Close the editor and run the macro from the Developer tab or the Macro dialog to confirm only rows from row 11 through one row above RowEnd are deleted.

Delete Rows with a Stored Row Range Instead of Selecting Rows

Result: Deleting the entire stored row range in one operation avoids selecting rows and reduces the chance of mismatched positions from earlier deletions.

Solution 2

Recalculate Match Positions After Earlier Deletions

Use this solution when your macro deletes rows in multiple passes or needs to find a dynamic end row after other rows have already been removed.

WPS Excel VBA fix

Delete Rows Between a Fixed Start and Dynamic End with WPS Excel VBA

In WPS Office Excel, store the fixed start row and the dynamically determined end row, then delete the entire target block with a single Range.EntireRow.Delete command after recalculating any match positions that follow deletions.

1. Open the VBA editor in WPS Office Excel: Press Alt+F11 to open the VBA editor and locate the module containing the row deletion macro.
2. Store the fixed start row: Add a variable such as StartRow = 11 before any deletion so the starting point is not affected by row shifts.
3. Recalculate the dynamic end row: Call WorksheetFunction.Match after earlier deletions or after the rows above the target range have changed, and assign the result to RowEnd.
4. Delete the target row block directly: Use WSN.Range("A" & StartRow & ":A" & RowEnd - 1).EntireRow.Delete without selecting the range.
5. Run and verify: Run the macro from the Developer tab or Macro dialog, then check that only rows outside the required start and end months are removed.

Prevents WorksheetFunction.Match errors caused by shifted row positionsUses a direct Range.EntireRow.Delete approach without selecting rowsWorks with standard Excel VBA macros in WPS OfficeMaintains compatibility with Microsoft Excel .xlsm and macro-enabled filesLets you reuse the corrected procedure for monthly template updates

Frequently Asked Questions

Why does WorksheetFunction.Match fail after deleting rows in Excel VBA?

Earlier row deletions shift the remaining cells upward, so a previously calculated row number or a match range may no longer point to the expected date. Recalculate the match after deletion or use a stored row range to delete the block safely.

How do I delete rows from row 11 to the row above a dynamic end row in WPS Excel VBA?

Use WSN.Range("A11:A" & RowEnd - 1).EntireRow.Delete. This removes the whole rows from row 11 through RowEnd - 1 without selecting the range first.

Do I need to select rows before deleting them in an Excel VBA macro?

No. Selecting or activating rows is unnecessary and can slow the macro or cause unexpected focus changes. Use the Range.EntireRow.Delete method directly.

Can WPS Office run Excel VBA macros that use WorksheetFunction.Match?

Yes. WPS Office Excel supports VBA macro functionality, including WorksheetFunction.Match, and can open common macro-enabled Excel file formats such as .xlsm.

How do I recalculate RowEnd after deleting rows in VBA?

Perform the WorksheetFunction.Match call after the earlier deletion has completed and point it at the current data range. Store the result in RowEnd, then delete from the fixed start row to RowEnd - 1.

Excel & Data Problems