How to Check for Duplicates Before Running an Excel VBA Macro in WPS Office
Question details
The user needs a VBA macro in WPS Spreadsheets that checks column A from row 10 to the last populated row for duplicate values, then runs a duplicate-removal procedure only when duplicates are found; otherwise it should display a message and stop.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet contains data in column A starting at row 10, and the user wants to conditionally run a duplicate-removal macro only if duplicates exist in that range.
- Observed behavior
- The macro should detect duplicates with COUNTIF, call the duplicate-removal routine when any value occurs more than once, or show “No duplicates exist. The macro will not run.” and exit.
Open the target workbook in WPS Spreadsheets and make sure macros are enabled. Press Alt+F11 to open the VBA editor and confirm the active sheet contains the data to check starting in column A at row 10.
Use a COUNTIF Duplicate Flag Before Running the Duplicate-Removal Macro
Use this method when you want the macro to scan column A from row 10 to the last used row, set a flag if any duplicate value is found, and only then run the duplicate-removal routine.
This approach uses WorksheetFunction.CountIf to compare each cell against the entire target range. It is the most reliable way to detect duplicate values for this VBA task because it checks the underlying cell values rather than colors or conditional formatting.
In WPS Spreadsheets, press Alt+F11 to open the VBA editor.
Click Insert > Module in the VBA editor menu to create a new module for the macro.
Paste the following code into the module: Sub DupCheck() Dim LastRow As Long, i As Long Dim Ws As Worksheet Dim Rng As Range Dim DupFlag As Boolean Set Ws = ActiveSheet LastRow = Ws.Range("A" & Ws.Rows.Count).End(xlUp).Row Set Rng = Ws.Range("A10:A" & LastRow) For i = 10 To LastRow If WorksheetFunction.CountIf(Rng, Ws.Cells(i, "A").Value) > 1 Then DupFlag = True Exit For End If Next i If DupFlag Then 'Run your Remove Duplicates macro here. Else MsgBox "No duplicates exist. The macro will not run." End If End Sub
Press F5 or click Run > Run Sub/UserForm while the cursor is inside the Sub, or close the editor and press Alt+F8 in WPS Spreadsheets, select DupCheck, and click Run.
Run a Duplicate-Check VBA Macro in WPS Spreadsheets
WPS Spreadsheets supports VBA macros, so you can open the VBA editor, add the COUNTIF duplicate-check macro, and let it conditionally call your duplicate-removal procedure. It works with existing Excel macro-enabled workbooks and requires no separate Microsoft Office installation.
- 1. Download and install WPS Office: Download WPS Office from the official WPS site and install it on your computer.
- 2. Open your macro-enabled workbook: Launch WPS Spreadsheets and open the .xlsm or .xlsx workbook that contains the duplicate-check macro.
- 3. Run or edit the macro: Press Alt+F11 to open the VBA editor, select the macro, and press F5 to run the duplicate check.
- 4. Use the result: If duplicates are found, the macro proceeds with duplicate removal; otherwise it shows the “No duplicates exist. The macro will not run.” message.
Frequently Asked Questions
Does this duplicate-check macro work in WPS Spreadsheets?
Yes. Open your workbook in WPS Spreadsheets, press Alt+F11 to open the VBA editor, insert a module, and paste the macro. The WorksheetFunction.CountIf call works the same way for .xls, .xlsx, and .xlsm files.
Why should I use COUNTIF instead of checking conditional formatting colors?
Conditional-formatting colors are not normally read directly by VBA for this logic, and Range.DisplayFormat cannot be used in a user-defined function. COUNTIF checks the actual cell values, so it reliably detects duplicates before the macro runs.
How do I make the macro call my existing duplicate-removal procedure?
In the If DupFlag Then block, replace the comment 'Run your Remove Duplicates macro here' with the name of your procedure, for example Call RemoveDuplicatesMacro.
What if the macro reports no duplicates but I can see repeated values?
Confirm your data starts at A10 and that the active sheet is the one being checked. Duplicate detection is based on cell values, so extra spaces, case differences, or numbers stored as text can cause values to be treated as different.
