How to Create an Excel Macro to Move Rows Based on Cell Values: VBA Development

Microsoft Excel is an incredibly versatile tool that can be significantly enhanced with the use of macros. One of the common tasks that can be streamlined with macros is moving rows to specific sheets based on certain cell values. This article will guide you through the process of creating such a macro using VBA (Visual Basic for Applications) in Excel.

Prerequisites: Basic Knowledge of VBA

Before diving into creating the macro, it's essential to have a basic understanding of VBA. Even if you're not a seasoned VBA developer, you should be familiar with the following:

Understanding how to open the VBA editor in Excel Basic concepts of variables and arrays in VBA How to use loops, conditional statements, and functions in VBA Standard structures in VBA like Sub, Function, and End Sub/Function

Scenario: Move Rows Based on Cell Values

Assume you have a worksheet named DataSheet with various rows of data. Depending on the value of a specific cell in each row, you want to move that entire row to another worksheet. For example, if a row contains the word "Pending" in column A, you want to move that entire row to the "PendingSheet".

Scenario 1: Move Rows Based on One Column

If the condition is based on the value of a single column, the process is a bit straightforward.

Input

Consider the following data in the DataSheet:

Column A Column B Column C Completed John Doe Research Pending Jane Smith Report Completed Alice Johnson Project Update

Output

Here's the desired output after running the macro:

The row containing "Pending" in Column A gets moved to the "PendingSheet". The "DataSheet" will only contain rows with "Completed".

Scenario 2: Move Rows Based on Any Cell in the Row

However, if the condition is based on the value appearing anywhere in the row, the macro needs to be more complex. For instance, if the word "Rejected" appears in any cell, the entire row gets moved to the "RejectedSheet".

Input

Here's an example of the input data:

Column A Column B Column C Completed David LLC Rejected Marketing Email Campaign Completed Laura Order

Output

The output should be:

The row containing "Rejected" anywhere in the row gets moved to the "RejectedSheet". The "DataSheet" will only contain rows with "Completed".

Step-by-Step Guide to Create the Macro

Step 1: Create a New VBA Module

1. Press ALT F11 to open the VBA editor.

2. In the Project Explorer window, right-click on the ThisWorkbook or the specific worksheet you want to work with and choose Insert > Module.

Step 2: Write the VBA Code

Below are the sample codes for both scenarios. You can copy and paste them into the module created in Step 1.

Scenario 1: Move Rows Based on One Column

Sub MoveRowsBasedOnCellValue()
    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim cellValue As String
    ' Define worksheets and the destination sheet
    Set wsSource  ("DataSheet")
    Set wsDestination  ("PendingSheet")
    ' Mark the last row in the source sheet
    lastRow  wsSource.Cells(, 1).End(xlUp).Row
    ' Loop through each row
    For i  1 To lastRow
        cellValue  wsSource.Cells(i, 1).Value
        ' Check if the cell value matches the criteria
        If cellValue  "Pending" Then
            ' Copy the entire row to the destination sheet
            (i).Copy Destination:(wsDestination.Cells(, 1).End(xlUp).Row   1)
            ' Delete the row from the source sheet
            (i).Delete
        End If
    Next i
End Sub

Scenario 2: Move Rows Based on Any Cell in the Row

Sub MoveRowsBasedOnAnyCellValue()
    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim j As Long
    Dim cellValue As String
    ' Define worksheets and the destination sheet
    Set wsSource  ("DataSheet")
    Set wsDestination  ("RejectedSheet")
    ' Mark the last row in the source sheet
    lastRow  wsSource.Cells(, 1).End(xlUp).Row
    ' Loop through each row
    For i  1 To lastRow
        ' Loop through each cell in the current row
        For j  1 To 3 ' Change the range as necessary
            cellValue  wsSource.Cells(i, j).Value
            ' Check if the cell value matches the criteria
            If cellValue  "Rejected" Or cellValue  "Failed" Then
                ' Copy the entire row to the destination sheet
                (i).Copy Destination:(wsDestination.Cells(, 1).End(xlUp).Row   1)
                ' Delete the row from the source sheet
                (i).Delete
                ' Exit the inner loop once a match is found to avoid double deletion
                Exit For
            End If
        Next j
    Next i
End Sub

Step 3: Run the Macro

1. Close the VBA editor and return to Excel.

2. Press ALT F8 to open the Macro dialog box.

3. Select the macro you just created and click Run.

Additional Tips and Considerations

1. **Error Handling**: It's always good practice to include error handling in your macros. For instance, if the destination sheet doesn't exist, you can create it before proceeding with the operation.

2. **Performance**: If you have a large dataset, consider optimizing your code for performance by using more advanced techniques like ranges instead of single cells or loops where necessary.

3. **Security**: When sharing your workbook, ensure that macros are enabled and the VBA code is not visible to others. This can be done by using the Macro Settings and Protect Workbook features.

Conclusion

Automating the process of moving rows in Excel based on cell values can significantly reduce manual effort and enhance operational efficiency. By following this guide, you can create powerful Excel macros to streamline your workflow and save valuable time.

Final Thoughts

Whether you need to move rows based on a single column or any cell in the row, VBA provides the flexibility to handle these tasks with ease. By creating these macros, you can effectively manage large datasets and ensure that your Excel sheets are organized and relevant to your needs.