Moving Rows Based on Matching Cells Across Two Worksheets

To move rows between Excel worksheets based on matching cell values, you must use a VBA macro that correctly identifies matches before deletion. A common error in existing scripts is an inverted logic check that deletes non-matching data instead of the intended rows. This guide provides a corrected script and step-by-step instructions to safely transfer or remove rows across sheets without corrupting your dataset.

Understanding the Logic Error

The primary reason previous attempts at this task fail is due to a subtle but critical bug in the conditional statement used to identify matches. In Visual Basic for Applications (VBA), when searching for a value using methods like Application.Match, the function returns an error value if the item is not found. The correct way to verify a match is to ensure the result is not an error. Many developers mistakenly write the condition as If Not IsError(...) Then but then apply the negation incorrectly to the entire block, or they use Is Nothing with objects rather than variant arrays. If the logic is reversed, the code interprets a failed search as a success. Consequently, it deletes rows that do not match the criteria, leaving only the data you wanted to keep, or vice versa depending on the specific implementation. To fix this, we must ensure that the deletion action only triggers when the lookup value is successfully found in the target range.

Preparation and Safety Measures

Before running any macro that modifies data structure, such as deleting rows, you must protect your original data. Deleting rows shifts all subsequent cells up, which can lead to index mismatches if you are iterating through a range from top to bottom. The most robust approach is to iterate backwards from the last row to the first. This ensures that deleting row i does not affect the row number of i-1. Additionally, always work on a copy of your workbook. While the script below includes safeguards, automated deletions are irreversible once saved. You should also verify that the column headers in both worksheets align logically. For instance, if you are matching based on an ID number in Column A of Sheet1, ensure that Column A in Sheet2 contains the same type of identifier. Mismatched data types, such as text versus numbers, will cause lookups to fail even if the visual representation looks identical.

The Corrected VBA Solution

The following code demonstrates how to move rows from a source sheet to a destination sheet based on a match in a specific column. It uses Application.Match which is faster than looping through ranges for large datasets. The key correction lies in the IsError check. We explicitly check if the result of the match is an error. If it is not an error, a match exists.

Sub MoveRowsBasedOnMatch()
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim matchResult As Variant
    Dim destRow As Long
    
    ' Set your worksheet names here
    Set wsSource = ThisWorkbook.Sheets("Sheet1")
    Set wsDest = ThisWorkbook.Sheets("Sheet2")
    
    ' Determine the last row in the source sheet
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    
    ' Start from the bottom and move upwards to avoid index shifting issues
    For i = lastRow To 2 Step -1
        ' Assume Column A holds the unique identifier to match
        
        ' Attempt to find the value in Column A of the Destination Sheet
        On Error Resume Next
        matchResult = Application.Match(wsSource.Cells(i, 1).Value, wsDest.Columns(1), False)
        On Error GoTo 0
        
        ' FIX: Check if Match was successful (Not an Error)
        If Not IsError(matchResult) Then
            ' A match was found. Now move the entire row.
            
            ' Find the next empty row in the destination sheet
            destRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row + 1
            
            ' Copy the row to the destination
            wsSource.Rows(i).Copy Destination:=wsDest.Rows(destRow)
            
            ' Delete the row from the source
            wsSource.Rows(i).Delete
        End If
    Next i
    
    MsgBox "Process complete.", vbInformation
End Sub

In this snippet, Application.Match searches for the value in wsSource.Cells(i, 1) within wsDest.Columns(1). If the value is found, matchResult contains the row number. If not, it contains an #N/A error. The line If Not IsError(matchResult) Then is the crucial fix. It ensures that the code inside the block only executes when a true positive match occurs. Without this precise check, or if the logic were inverted, you would delete the wrong data.

Customizing the Script

You may need to adjust the column references depending on your data layout. Change wsSource.Cells(i, 1) to reference the column containing the key identifier. Similarly, update wsDest.Columns(1) to match the corresponding column in the destination sheet. If you are matching multiple columns simultaneously, you might need to concatenate the keys into a single string or use a dictionary object for more complex lookups. For users who prefer not to modify the source sheet but simply want to copy matching rows, you can remove the wsSource.Rows(i).Delete line. This allows you to create a consolidated report in Sheet2 while keeping Sheet1 intact. Always test the macro on a small subset of data first. Verify that the rows being moved correspond exactly to the IDs or values you expect.

Troubleshooting Common Issues

If the macro runs but no rows move, check for leading or trailing spaces in your text data. Application.Match is sensitive to exact string matches. Use the TRIM function in Excel to clean your data before running the script. Another common issue is hidden rows; the script iterates through visible and hidden rows alike. If you have filtered data, unfilter the sheet before running the macro to ensure all relevant rows are processed. Refer to our guide on Excel Pausing, Crashing, Pasting, Editing, Formula Bar Issues if you experience performance lags during execution. Large datasets can cause VBA to freeze temporarily. Increasing the screen updating state or disabling calculation mode can improve speed, though the basic script above is optimized for standard usage.

Frequently Asked Questions

Why does my macro delete the wrong rows?

This usually happens because the logical condition for identifying a match is inverted. Ensure you are using Not IsError(...) to confirm a match exists before triggering the delete command. An incorrect check treats failures as successes.

Can I move rows based on multiple column matches?

Yes, but you must combine the values from multiple columns into a single unique key. Concatenate the values from each column into one string and use that combined string as the lookup value in both the source and destination sheets.

What if my data contains blank cells?

Blank cells can cause false positives or errors in matching. Filter out blank rows before running the macro or add a condition to skip rows where the key column is empty. This prevents accidental movement of incomplete records.

How do I stop the macro from asking for confirmation?

By default, VBA does not ask for confirmation when deleting rows. If you are seeing prompts, you may have enabled alerts via Application.DisplayAlerts = True. Set this to False at the start of your macro and reset it to True at the end to suppress warnings.

Is there a non-VBA alternative to this task?

You can use Power Query to merge tables based on key columns. Import both sheets into Power Query, perform a left anti join or inner join depending on your needs, and load the result back to Excel. This method is dynamic and updates automatically when source data changes.