VBA & Macro Problems

How to Copy Ranked Rows Excluding Ties with a VBA Macro in Excel

Amos Gikunda Sep 29, 2026 869 views

Question details

The user needs a VBA macro to copy specific data fields based on rank, excluding rows ranked 1 (including ties), and to copy the first row ranked above 2 from another range.

How to Copy Ranked Rows Excluding Ties with a VBA Macro in Excel

Product: Spreadsheet
Device & OS: not provided
Scenario: Automating the transfer of bracket picks or ranked data into designated destination rows based on specific numerical rank conditions.
Observed behavior: Manual copying is required without a macro. The goal is to automatically evaluate ranks and transfer data from specific cells (X30:AD34 and X22:AD24) to target cells (A44:E47 and A48:E48).

Before you start

Verify that your source data is strictly located in ranges X30:AD34 and X22:AD24, and ensure the Developer tab is enabled in your ribbon menu to access the VBA editor.

Solution 1Recommended

Run Custom VBA Script to Filter and Copy Ranked Rows

Implement a VBA macro that loops through the specified rows, evaluates the rank in column AD, and conditionally transfers the designated fields to your destination ranges.

The following script clears the previous contents in your destination ranges (A44:E47 and A48:E48). It then iterates through rows 30 to 34, excluding rank 1 (and ties), copying the specified columns to row 44 onwards. Finally, it copies the first valid record from rows 22 to 24 into row 48.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor in your spreadsheet.

Insert a New Module

In the top menu, click on Insert and select Module from the dropdown to create a blank code window.

Paste the VBA Macro

Copy and paste the following code into the module window: Sub CopyBracketPicks() Dim ws As Worksheet, r As Long, destRow As Long Set ws = ActiveSheet ws.Range("A44:E47").ClearContents ws.Range("A48:E48").ClearContents destRow = 44 For r = 30 To 34 If Val(ws.Range("AD" & r).Value) <> 1 Then ws.Range("A" & destRow).Value = ws.Range("X" & r).Value ws.Range("B" & destRow).Value = ws.Range("Y" & r).Value ws.Range("C" & destRow).Value = ws.Range("Z" & r).Value ws.Range("E" & destRow).Value = ws.Range("AB" & r).Value destRow = destRow + 1 If destRow > 47 Then Exit For End If Next r For r = 22 To 24 If Val(ws.Range("AD" & r).Value) > 2 Then ws.Range("A48").Value = ws.Range("X" & r).Value ws.Range("B48").Value = ws.Range("Y" & r).Value ws.Range("C48").Value = ws.Range("Z" & r).Value ws.Range("E48").Value = ws.Range("AB" & r).Value Exit For End If Next r End Sub

Run the Macro

Close the VBA editor. Press ALT + F8 to open the Macro dialog box, select CopyBracketPicks from the list, and click Run.

Run Custom VBA Script to Filter and Copy Ranked Rows

Data Copied Successfully: Your designated fields will instantly appear in rows 44 through 48 based on the ranking logic you established.

Advanced Spreadsheet Features

Execute VBA Macros Seamlessly in WPS Spreadsheet

WPS Office offers native support for VBA macros, allowing you to automate complex tasks like filtering and copying ranked rows with ease.

1. Open Your Spreadsheet: Launch WPS Spreadsheet and open the file containing your ranked data.
2. Access Developer Tools: Navigate to the Developer tab on the top ribbon menu to access macro features.
3. Run Macros: Click the Macros button or press ALT + F8 to manage, edit, and execute your custom VBA scripts directly.

High compatibility with Microsoft Excel VBA scripts and modulesLightweight installation with fast macro execution speedsFamiliar spreadsheet interface to easily manage developer tools

Frequently Asked Questions

Why is the VBA macro not running in my spreadsheet?

Your macro security settings might be blocking execution. Navigate to the Developer tab, select Macro Security, and adjust your settings to enable macros for trusted documents.

How can I modify this macro for different row ranges?

You can change the row ranges by editing the `For r = 30 To 34` and `For r = 22 To 24` lines in the script. Ensure you also adjust the column letters and destination variables (`destRow`) to match your new layout.

Can I undo a VBA macro action after it runs?

Generally, running a VBA macro clears the undo history in your spreadsheet. Because macro actions cannot easily be reversed, it is highly recommended to save a copy of your workbook before running new code.

Excel & Data Problems