VBA & Macro Problems

Fix Excel for Mac VBA Not Deleting Table Rows

WPS Editor Oct 1, 2026 869 views

Question details

The user needs to resolve an issue where running a VBA macro to delete a table's data rows fails to clear the entire table correctly.

How to Fix Excel for Mac Not Deleting Rows from a Table Using VBA

Product: Excel
Device & OS: Mac
Scenario: Deleting data rows from an Excel table using VBA macro commands like DataBodyRange.Delete.
Observed behavior: Deleting the table's DataBodyRange clears most rows but leaves data in row 2, and the table remains at its original size even after recreating it.

Before you start

Before modifying your macros, ensure you have saved a backup copy of your workbook to prevent accidental data loss during VBA script testing.

Solution 1Recommended

Test Macro in a Clean Workbook and Verify VBA Syntax

Isolate the issue by testing the VBA code in a brand-new workbook and ensure you are using the correct deletion method for your specific goal.

Sometimes, workbooks can develop structural corruption that prevents macros from executing properly. Copying your data to a clean file helps rule out file-specific glitches.

Additionally, understanding the distinction between targeting the table object and targeting the worksheet rows is crucial for resolving deletion behaviors.

Create a New Workbook

Open Excel for Mac and create a completely blank workbook to serve as your testing environment.

Migrate Data and Code

Copy the specific worksheet containing your table and paste it into the new workbook. Then, open the VBA Editor and copy your macro module over.

Adjust VBA Syntax

Review your VBA code. If you only want to clear the table's data cells, ensure you are referencing 'DataBodyRange'. If you want to delete the actual worksheet rows, change your code to use 'DataBodyRange.EntireRow.Delete'.

Run the Macro

Execute the macro in the new workbook. Check if the table resizes correctly and verify that row 2 is properly cleared.

Test Macro in a Clean Workbook and Verify VBA Syntax

Submit a Sample File: If the issue persists in a new workbook, it may be a version-specific bug in Excel for Mac. Provide a small sample workbook to the support community with your exact Excel version number for further investigation.

Free Microsoft Office alternative

Try WPS Office for Smooth Spreadsheet Management

If Excel for Mac continues to struggle with table formatting or macro glitches, consider switching to WPS Office. It offers a lightweight, fast, and highly compatible spreadsheet experience.

1. Download WPS Office: Visit the official WPS website and download the free WPS Office installer for your Mac.
2. Install and Launch: Follow the on-screen instructions to install the application, then launch WPS Spreadsheets.
3. Open Your Excel File: Open your existing .xlsx workbook in WPS Spreadsheets to manage your data tables with ease.

Completely free and lightweight office suiteHigh compatibility with Microsoft Excel (.xlsx, .xls, .csv) formatsFamiliar user interface ensuring seamless migration and zero learning curveRobust data management tools for handling complex tables effortlessly

microsoft office alternative - wps office

Frequently Asked Questions

What is the difference between DataBodyRange and EntireRow in VBA?

DataBodyRange refers specifically to the data cells contained within a ListObject (table). Modifying it only affects the table. EntireRow, however, references the complete worksheet row that spans across all columns, extending beyond the table boundaries.

Why does Excel leave an empty row in my table when I delete all rows?

Excel tables (ListObjects) are designed to always retain at least one data row so the table structure and its formulas remain intact. If you try to delete all rows via VBA, Excel will often preserve the first row (row 2) to prevent the table object from breaking.

Can a large workbook size cause VBA row deletion to fail?

While exceptionally large workbooks can slow down macro execution or cause memory issues, they do not typically cause a specific failure like leaving a single row behind. This behavior is more likely tied to syntax logic, table object rules, or file corruption.

Excel & Data Problems