VBA & Macro Problems

How to Remove Duplicate Rows in Excel Using a VBA Macro (Dynamic Used Range)

Elise Williams Oct 12, 2026 872 views

Question details

The user needs an Excel VBA macro that automatically detects a variable-length dataset's used range and removes duplicate rows.

How to Remove Duplicate Rows in Excel Using a VBA Macro

Product: Excel
Device & OS: not provided
Scenario: Cleaning up variable-length reports that are combined onto a single worksheet by removing duplicate entries dynamically.
Observed behavior: The current macro fails because it relies on a hardcoded, fixed range, which prevents it from capturing the entire dataset when the length of the report varies.

Before you start

Verify that your dataset has a clear header row and ensure your data does not contain trailing spaces, as these can affect duplicate detection.

Solution 1Recommended

Use a VBA Macro to Dynamically Detect and Remove Duplicates

This solution uses the worksheet's UsedRange property to automatically capture the entire dataset, regardless of length, and builds an array to evaluate all columns for duplicates.

To handle variable-length reports, the macro must dynamically identify the range of data containing values. By utilizing the ActiveSheet.UsedRange property alongside a dynamic array for column indexes, the VBA procedure ensures no row is left unchecked.

Open the VBA Editor

Open your Excel workbook and press ALT + F11 to launch the Visual Basic for Applications (VBA) editor.

Insert a New Module

In the VBA editor, click on 'Insert' in the top menu and select 'Module' to create a blank script window.

Paste the Macro Code

Copy and paste the following code into the module: Sub RemoveDuplicates() Dim ws As Worksheet Dim rng As Range Dim columns As Variant Dim i As Long Set ws = ActiveSheet Set rng = ws.UsedRange ReDim columns(0 To rng.Columns.Count - 1) For i = 0 To rng.Columns.Count - 1 columns(i) = i + 1 Next i rng.RemoveDuplicates Columns:=columns, Header:=xlYes End Sub

Run the Macro

Close the VBA editor. Press ALT + F8 in Excel, select 'RemoveDuplicates' from the macro dialog box, and click 'Run'. This should be executed after your data-import and report-combination steps.

Use a VBA Macro to Dynamically Detect and Remove Duplicates

Header Row Requirement: The provided code assumes your data includes a header row (Header:=xlYes). If your data does not have headers, change this parameter to Header:=xlNo before running the script.

WPS Office for Macros

Run VBA Macros Seamlessly in WPS Office Spreadsheets

WPS Office Spreadsheets provides robust support for standard Excel VBA macros, allowing you to run your dynamic duplicate removal scripts without rewriting code. It is an excellent, lightweight environment for processing combined datasets.

1. Open your dataset in WPS Spreadsheets: Launch WPS Office and open your .xlsm or .xlsx workbook containing the variable-length reports.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon to view macro tools. If hidden, enable it in the WPS Office settings.
3. Paste your VBA Code: Click 'Visual Basic' or press ALT + F11, insert a new Module, and paste the dynamic UsedRange macro provided above.
4. Run the Duplicate Removal: Execute the macro to instantly detect the full data boundaries and remove duplicate rows automatically.

High compatibility with Microsoft Excel VBA macros and .xlsm formatsLightweight, fast, and free to use for daily spreadsheet tasksBuilt-in 'Remove Duplicates' tool accessible from the Data ribbon for quick cleanups without code

microsoft office alternative - wps office

Frequently Asked Questions

Why does the macro fail if there are blank rows above my data?

The UsedRange property evaluates the entire area of the sheet that has been modified, which might include blank rows at the top. If your actual data starts on a lower row, you may need to adjust the starting row of your range dynamically using 'rng.Offset' or explicitly define the starting cell.

Can I modify the macro to only check specific columns for duplicates?

Yes. Instead of using a loop to build an array of all columns, you can define the 'columns' variable with specific indexes. For example, use 'rng.RemoveDuplicates Columns:=Array(1, 3), Header:=xlYes' to check only the first and third columns.

Does WPS Office support Excel VBA macros?

Yes, WPS Office supports standard VBA macros. Users can seamlessly run, edit, and create VBA scripts, including data cleanup and duplicate removal procedures, directly within the WPS Spreadsheets environment.

Excel & Data Problems