VBA & Macro Problems

Randomly Select and Remove Matching Names in Excel with VBA

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to create a VBA macro that can randomly choose a name from a populated list, record that chosen name in a log, and then delete every occurrence of that name (including any duplicates) from the original list.

Product: Excel
Device & OS: not provided
Scenario: Automating the process of randomly picking a winner or candidate from a dataset, documenting the selection, and cleaning up the source list by removing all related entries.
Observed behavior: Without a macro, the process of randomly picking an item, logging it, and manually filtering to delete all duplicate matches is tedious and error-prone.

Before you start

Before running any VBA macro that modifies or deletes data, always create a backup copy of your workbook. Macro actions cannot typically be undone using the standard Undo function.

Solution 1Recommended

Use a VBA Macro with Randomize and a Backward Loop

Write a VBA script that generates a random row index, stores the value found in that row, and loops backward through the list to delete all matching duplicates.

By utilizing the Randomize function, VBA can pick a truly random row from your dataset. Once the target name is identified, it is written to a designated logging column.

Crucially, to delete multiple rows matching that name, the macro must loop from the bottom of the list upward. If it loops downward, deleting a row shifts the remaining rows up, which causes the macro to skip adjacent duplicate entries.

Open the VBA Editor

Press 'Alt + F11' in your Excel workbook to launch the Visual Basic for Applications (VBA) editor.

Insert a New Module

Click 'Insert' in the top menu and select 'Module' to create a blank workspace for your macro.

Write the Macro Code

Write your script using the 'Randomize' function to pick a random row between 1 and the last populated row. Set a variable to store the cell's value, write that value to your log column (e.g., Column C), and write a 'For i = LastRow To 1 Step -1' loop that checks if the cell in Column A matches the variable. If it does, use 'Rows(i).Delete'.

Run the Macro

Close the VBA editor, return to your worksheet, and press 'Alt + F8'. Select your newly created macro from the list and click 'Run'.

Why Loop Backward?: When using VBA to delete rows, you must always loop upward (using 'Step -1'). This ensures that deleting a row does not change the row numbers of the unchecked data above it.

Efficient Data Automation

Run Macros and Automate Tasks in WPS Spreadsheet

WPS Spreadsheet offers excellent support for VBA macros in its advanced versions, allowing you to easily automate tasks like randomizing, logging, and deleting data without switching applications.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the data list.
2. Access Developer Tools: Navigate to the 'Developer' tab on the ribbon and click on 'Visual Basic Editor'.
3. Insert Your Macro: Insert a Module, paste your randomization and deletion macro code, and save the script.
4. Execute the Code: Run the macro to automatically pick a random name, log it, and remove all matches from your sheet.

Highly compatible with Microsoft Excel VBA macros (.xlsm files)Built-in Developer tools to easily write, edit, and debug scriptsFast and lightweight execution of complex data processing tasksFamiliar spreadsheet interface that requires zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

How do I enable the Developer tab to access VBA?

Go to File > Options > Customize Ribbon. In the right-hand pane, check the box next to 'Developer', then click OK. The Developer tab will now appear on your top ribbon, giving you access to the Visual Basic Editor and Macros.

Can I undo a VBA macro after it deletes data?

No, actions executed by a VBA macro usually clear the Undo history and cannot be reversed using Ctrl + Z. Always run macros on a backup copy of your dataset first to ensure it works correctly without risking data loss.

Will this macro work for numbers as well as text?

Yes. The macro compares the exact cell value. Whether your list contains text names, numerical IDs, or alphanumeric codes, it will successfully identify and remove all identical matches.

Why did my macro skip some duplicate names?

If your macro missed duplicates, it is likely because the loop iterated downward (e.g., from row 1 to 100) instead of upward. Adjust your For loop to iterate backward using 'Step -1' to prevent row index shifting upon deletion.

Excel & Data Problems