How to Move Rows Based on Cell Status in Excel

Have you ever wanted to move rows in one worksheet based on the status of another? Here’s how to do that in Excel. I’m including my Visual Basic code, which automates the process. This allows you to automatically move rows based on the status of another worksheet in Excel.

VBA Code for Moving Rows in Excel

I’ll start with step-by-step instructions for the process. Note that I already covered how to move rows in Excel. That’s my basic guide. If you just want to swap row positions, it will point you in the right direction. Here, I’m automating that process based on the contents or status of another cell.

I’ll also include the code to input, which allows you to create your own macro. Just like my guide on removing duplicates in Excel, there are concise steps below. Follow along!

Open Excel and the VBA Editor

First, Open Excel and enable the developer tab. If you don’t see the developer tab, you’ll need to enable it.

Go to File > Options > Customize Ribbon

Then, check the Developer option and click OK.

Next, open the VBA Editor. To do this, click the developer tab and click “Visual Basic”. That opens the editor window.

Insert a New Module

Inside the VBA editor, click Insert > Module. That creates a new module.

You can copy and paste my code into the module. In short, it moves rows from Sheet1 to Sheet2 based on a change in the status in column A. You can alter the code from Sheet1 and Sheet2 to your own sheet names. And you can change the column, too.

Sub MoveRowsBasedOnStatus()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng As Range
Dim cell As Range
Dim lastRow1 As Long
Dim lastRow2 As Long

' Define worksheets
Set ws1 = ThisWorkbook.Sheets("Sheet1") ' Source sheet
Set ws2 = ThisWorkbook.Sheets("Sheet2") ' Destination sheet

' Find the last row with data in the source sheet
lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row

' Loop through each cell in the specified column (e.g., column A)
For Each cell In ws1.Range("A1:A" & lastRow1)
' Check if the cell value matches the status (e.g., "Complete")
If cell.Value = "Complete" Then
' Find the last row in the destination sheet
lastRow2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row + 1

' Copy the entire row to the destination sheet
cell.EntireRow.Copy Destination:=ws2.Rows(lastRow2)

' Clear the original row in the source sheet
cell.EntireRow.ClearContents
End If
Next cell

' Optional: Remove empty rows in the source sheet
On Error Resume Next
ws1.UsedRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

Here’s what it means:

• Define Worksheets: The code defines two worksheets, one as the source (Sheet1) and the other as the destination (Sheet2).
• Find Last Rows: It finds the last row with data in the source sheet to loop through.
• Loop and Check Status: It loops through each cell in the specified column (e.g., column A). If the cell value matches the desired status (e.g., “Complete”), it copies the entire row to the destination sheet.
• Clear Original Row: It clears the contents of the original row in the source sheet.
• Remove Empty Rows: Optionally, it removes any empty rows in the source sheet to keep it tidy.

Run The Macro

Refining the Macro

The macro should run fine exactly as it is, but you may see empty rows in some instances. It also doesn’t automatically run when you write “complete” in a cell. Finally, you may want to move the macro from one sheet to another by changing the sheet names. Remember that you can track changes while you work.

In these cases, you’ll want to do the following:

In the VBA editor, find the worksheet you want to monitor (e.g., Sheet1) in the Project Explorer window. Double-click on the sheet name to open its code window.

Then, paste the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim StatusColumn As String
    Dim StatusValue As String
    Dim Cell As Range
    Dim lastRowDest As Long
   
    ' Set your variables
    Set wsSource = ThisWorkbook.Sheets("Sheet1") ' Source sheet name
    Set wsDest = ThisWorkbook.Sheets("Sheet2") ' Destination sheet name
    StatusColumn = "A" ' Column with the status
    StatusValue = "Complete" ' Status to check for

    ' Check if the change is in the status column
    If Not Intersect(Target, wsSource.Columns(StatusColumn)) Is Nothing Then
        ' Loop through each changed cell in the status column
        For Each Cell In Target
            If Cell.Value = StatusValue Then
                ' Find the last row in the destination sheet
                lastRowDest = wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Row + 1
                ' Copy the entire row to the destination sheet
                Cell.EntireRow.Copy Destination:=wsDest.Rows(lastRowDest)
                ' Delete the original row
                Cell.EntireRow.Delete
            End If
        Next Cell
    End If
End Sub

VBA Code for Moving Data Between Different Sheets

Insert the Code in a Standard Module:

Just like you did before, you’ll open the VBA editor and create a new module.

Paste the following code:

Sub MoveRowsBasedOnStatus(wsSourceName As String, wsDestName As String, StatusColumn As String, StatusValue As String)
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim rng As Range
    Dim Cell As Range
    Dim lastRowSource As Long
    Dim lastRowDest As Long
   
    ' Set your worksheets
    Set wsSource = ThisWorkbook.Sheets(wsSourceName)
    Set wsDest = ThisWorkbook.Sheets(wsDestName)
   
    ' Find the last row with data in the source sheet
    lastRowSource = wsSource.Cells(wsSource.Rows.Count, StatusColumn).End(xlUp).Row
   
    ' Loop through each cell in the specified column
    For Each Cell In wsSource.Range(StatusColumn & "1:" & StatusColumn & lastRowSource)
        ' Check if the cell value matches the status
        If Cell.Value = StatusValue Then
            ' Find the last row in the destination sheet
            lastRowDest = wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Row + 1
            ' Copy the entire row to the destination sheet
            Cell.EntireRow.Copy Destination:=wsDest.Rows(lastRowDest)
            ' Delete the original row
            Cell.EntireRow.Delete
        End If
    Next Cell
End Sub

Running the Module Macro

To run the module macro for different sheets and statuses, you can call it with:

Sub TestMoveRows()
    ' Example usage:
    MoveRowsBasedOnStatus "Sheet2", "Sheet3", "A", "Approved"
End Sub

Here’s an explanation of the code:

• Automatic Move on Status Change: The Worksheet_Change event macro in the worksheet module ensures rows are moved automatically when the status changes.
• Removing Empty Rows: The macro deletes the original row after moving it, preventing empty rows.
• Flexible Function for Different Sheets: The MoveRowsBasedOnStatus subroutine in a standard module allows you to move rows based on status changes between sheets and statuses by specifying parameters.

Conclusion

I hope this helps! This guide came to fruition because one of our readers asked about moving rows based on status in another cell in Excel. If you have any questions, please let me know in the comments.

On a related note, I also have a guide on using the consolidate feature in Excel. It’s helpful for consoldating data into a single worksheet!