VBA & Macro Problems

Delete Excess Rows Based on Nonblank Cells using Excel VBA

Steven ParkerSteven Parker Nov 3, 2026 868 views

Question details

The user wants a VBA macro to count nonblank cells within repeating 12-cell intervals and delete the surplus rows below them while keeping a specific number of rows based on the nonblank count.

Delete Excess Rows Based on Nonblank Cells Using VBA
Product
Excel
Device & OS
not provided
Scenario
Managing large grouped datasets that repeat every 13 rows (e.g., headers at rows 49, 62, 75). The required number of data rows in each block depends on the count of nonblank cells in a specific range like D49:N49.
Observed behavior
Standard top-down deletion macros fail and delete valid data because removing rows shifts the subsequent repeating ranges upward, ruining the static row offsets.
Before you start

Before running any row-deletion VBA macro, create a backup copy of your worksheet, as VBA execution clears the Undo stack and incorrectly calculated offsets will result in permanent data loss.

Solution 1Recommended

Use a Bottom-Up VBA Loop to Delete Surplus Rows

Process the worksheet from the bottom upward. This ensures that deleting rows at the bottom of the dataset does not shift the row numbers of the unprocessed blocks above.

When dealing with static repeating blocks (e.g., every 13 rows), a top-down macro will fail as soon as the first deletion occurs because row 62 will suddenly become row 59. By using a 'Step -13' loop starting from the last block, the offsets remain perfectly intact for the blocks above it.

You will use the 'WorksheetFunction.CountA' method to calculate how many nonblank cells exist in your target range (e.g., D49:N49), which tells the macro how many rows to keep.

1
Open the VBA Editor

Press 'Alt + F11' to launch the Visual Basic for Applications editor. Click 'Insert' > 'Module' to create a blank script window.

2
Define the Loop Variables

Declare your row variables. Find the last block row, then create a loop that steps backward. For example: 'For i = LastRow To 49 Step -13'.

3
Count Nonblank Cells

Inside the loop, use 'NonBlankCount = Application.WorksheetFunction.CountA(Range("D" & i & ":N" & i))' to determine how many rows you need to keep below the current header.

4
Calculate and Delete Surplus Rows

Determine the surplus by subtracting the nonblank count from the maximum allowed rows (e.g., 12). If there is a surplus, define the range to delete using an offset, such as 'Rows(i + NonBlankCount + 1 & ":" & i + 12).EntireRow.Delete'.

Use a Bottom-Up VBA Loop to Delete Surplus Rows
Testing on a Sample: Use 'F8' to step through your macro one line at a time on your test sheet. This allows you to visually verify that the correct range (e.g., D75:N75) is being calculated before the rows are actually deleted.

Automate Your Worksheets Easily with WPS Spreadsheet

WPS Spreadsheet provides excellent support for VBA and macros in its advanced and enterprise versions, allowing you to run complex automated scripts like bottom-up row deletion. Enjoy a lightweight interface with powerful data processing capabilities.

  1. 1. Open your macro-enabled file: Launch WPS Spreadsheet and open your .xlsm or .xlsx file containing the dataset you need to clean.
  2. 2. Access the Developer Tools: Click on the 'Developer' tab in the top ribbon and select 'Visual Basic' or 'Macros' to open the code editor.
  3. 3. Run your deletion script: Paste your bottom-up VBA code into the module and click 'Run' to seamlessly delete the excess rows based on your nonblank cell criteria.
Fully compatible with Microsoft Excel macro-enabled workbooks (.xlsm)Integrated Developer tab for writing, debugging, and running VBA codeHigh processing speed and stability when handling large, grouped datasetsFree to download with a highly intuitive, user-friendly interface
microsoft office alternative - wps office

Frequently Asked Questions

Why must the VBA macro loop from the bottom upward when deleting rows?

When you delete rows from the top down, the row numbers of all subsequent rows shift upward. This breaks fixed offsets (like repeating every 13 rows) and causes the macro to process the wrong ranges. Looping backward ensures the remaining unprocessed rows maintain their original row numbers.

How do I count nonblank cells in a specific range using VBA?

You can use the 'CountA' worksheet function within VBA. For example, 'Application.WorksheetFunction.CountA(Range("D49:N49"))' will return the exact integer representing how many cells in that range are not empty.

What should I do if my macro deletes all the data after a certain row?

This usually indicates an error in your row offset calculation or loop step parameters. Ensure your For Loop steps backwards (e.g., 'Step -13') and that your deletion range explicitly calculates the offset from the active block's base row index. Always test offset logic on a backup copy.