VBA & Macro Problems

How to Remove Duplicates From a Variable-Length Excel Data Range

Jason Miller Oct 26, 2026 869 views

Question details

The user needs a VBA macro to remove duplicate entire rows based on a specific column in a dynamically sized or variable-length Excel data range.

How to Remove Duplicates From a Variable-Length Excel Data Range using VBA

Product: Excel
Device & OS: not provided
Scenario: Writing a VBA macro to clean up data by removing duplicate records across multiple columns without hardcoding row limits.
Observed behavior: Using a fixed row range or a single-column range only deletes cells in that specific range instead of removing the entire row across all relevant data columns.

Before you start

Before running the macro, ensure you have saved a backup copy of your workbook, as VBA data manipulations cannot be undone using the standard Undo shortcut.

Solution 1Recommended

Use Full Column References in the RemoveDuplicates Method

Specify the entire column range so that Excel automatically handles variable row lengths and deletes the complete row across all data columns.

When dealing with a variable number of rows, referencing the entire columns bypasses the need to find the last row dynamically. The Columns parameter specifies the index of the column within the range to check for duplicates.

Open the VBA Editor

Press Alt + F11 in Excel to launch the VBA Editor window.

Insert the Macro Code

Navigate to the module containing your macro and input the following code: ActiveSheet.Range("A:CC").RemoveDuplicates Columns:=20, Header:=xlYes

Adjust the Range and Column Index

Modify 'A:CC' to cover all the columns in your dataset. Update the 'Columns:=' value to match the relative numerical index of your target column within that range (e.g., use 20 for column T if the range starts at A).

Run the Macro

Press F5 or run the subroutine from the editor. Excel will process the entire length of the specified columns and remove duplicate rows based on your target column.

Use Full Column References in the RemoveDuplicates Method

Headers Included: Setting Header:=xlYes ensures your header row is excluded from the duplicate evaluation and will not be accidentally deleted.

Solution 2

Calculate the Last Used Row Dynamically

Identify the last row with data using VBA to define a precise range instead of selecting entire columns, which is useful for optimizing macro performance in large datasets.

Built-in Feature

Easily Remove Duplicates in WPS Spreadsheet Without Code

If you prefer not to write or troubleshoot VBA macros, WPS Spreadsheet offers a built-in 'Remove Duplicates' feature that automatically detects your variable-length data range and allows you to select specific columns for comparison in just a few clicks.

1. Open Your File in WPS: Launch WPS Office and open your dataset in WPS Spreadsheet.
2. Select the Data Range: Click any cell within your data range. WPS will automatically detect the contiguous data.
3. Navigate to the Data Tab: Click on the 'Data' tab located on the top ribbon menu.
4. Click Remove Duplicates: Select 'Remove Duplicates' from the toolbar. A dialog box will appear showing all detected columns.
5. Configure and Execute: Check 'My data has headers', select only the specific columns you want to evaluate for duplicates, and click 'Remove Duplicates'.

No coding required to remove duplicatesAutomatically detects variable-length data rangesFully compatible with Microsoft Excel files (.xlsx and .xlsm)Lightweight, fast performance, and a familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my macro only delete duplicate cells instead of the entire row?

This happens if your range is restricted to a single column (e.g., Range("$B:$B")). To delete the entire row, your range must span across all data columns (e.g., Range("$A:$CC")), while specifying the duplicate-checking column in the Columns:= parameter.

What does the Columns:= parameter mean in the RemoveDuplicates method?

The Columns parameter refers to the relative column index within your specified range, not the absolute worksheet column. If your range starts at column A, Columns:=20 checks the 20th column (column T) for duplicates.

Can I check multiple columns for duplicates simultaneously in VBA?

Yes, you can pass an array to the Columns parameter in VBA. For example, using Columns:=Array(1, 2) will check for duplicates based on the combination of both the first and second columns of your defined range.

Excel & Data Problems