excel - Pull merged rows based on a condition to a new page - Stack Overflow

admin管理员组

文章数量:1122846

I have a code that will pull single rows to other pages in the workbook based on meeting a condition. However, if I have merged rows that meet the condition it will not pull to the respective page and throws an error in the primary page.

I am completely new to vba and this is my first code so I'm sure I am way off on a lot of things.

Code is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    A = Worksheets("Open Tasks").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To A
        If Worksheets("Open Tasks").Cells(i, 7).Value = "Closed - Complete" Then
            Worksheets("Open Tasks").Rows(i).Cut
            Worksheets("Closed - Complete").Active
            B = Worksheets("Closed - Complete").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("Closed - Complete").Cells(B + 1, 1).Select
            ActiveSheet.Paste
            Worksheets("Open Tasks").Active
        End If

    Next

    For i = 2 To A
        If Worksheets("Open Tasks").Cells(i, 7).Value = "Canceled" Then
            Worksheets("Open Tasks").Rows(i).Cut
            Worksheets("Closed - Other").Active
            B = Worksheets("Closed - Other").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("Closed - Other").Cells(B + 1, 1).Select
            ActiveSheet.Paste
            Worksheets("Open Tasks").Active
        End If

    Next

    For i = 2 To A
        If Worksheets("Open Tasks").Cells(i, 7).Value = "Closed - Incomplete" Then
            Worksheets("Open Tasks").Rows(i).Cut
            Worksheets("Closed - Other").Active
            B = Worksheets("Closed - Other").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("Closed - Other").Cells(B + 1, 1).Select
            ActiveSheet.Paste
            Worksheets("Open Tasks").Active
        End If

    Next

    For i = 2 To A
        If Worksheets("Open Tasks").Cells(i, 7).Value = "Closed - OBE" Then
            Worksheets("Open Tasks").Rows(i).Cut
            Worksheets("Closed - Other").Active
            B = Worksheets("Closed - Other").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("Closed - Other").Cells(B + 1, 1).Select
            ActiveSheet.Paste
            Worksheets("Open Tasks").Active
        End If

    Next
End Sub

The above code will pull single rows, but when I have multiple rows that are merged and meet the conditions, I get an error:

"Run-Time Error "1004"
We Can't Do That To A Merged Cell  

Primary Work Space/Sheet

I have a code that will pull single rows to other pages in the workbook based on meeting a condition. However, if I have merged rows that meet the condition it will not pull to the respective page and throws an error in the primary page.

I am completely new to vba and this is my first code so I'm sure I am way off on a lot of things.

Code is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    A = Worksheets("Open Tasks").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 2 To A
        If Worksheets("Open Tasks").Cells(i, 7).Value = "Closed - Complete" Then
            Worksheets("Open Tasks").Rows(i).Cut
            Worksheets("Closed - Complete").Active
            B = Worksheets("Closed - Complete").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("Closed - Complete").Cells(B + 1, 1).Select
            ActiveSheet.Paste
            Worksheets("Open Tasks").Active
        End If

    Next

    For i = 2 To A
        If Worksheets("Open Tasks").Cells(i, 7).Value = "Canceled" Then
            Worksheets("Open Tasks").Rows(i).Cut
            Worksheets("Closed - Other").Active
            B = Worksheets("Closed - Other").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("Closed - Other").Cells(B + 1, 1).Select
            ActiveSheet.Paste
            Worksheets("Open Tasks").Active
        End If

    Next

    For i = 2 To A
        If Worksheets("Open Tasks").Cells(i, 7).Value = "Closed - Incomplete" Then
            Worksheets("Open Tasks").Rows(i).Cut
            Worksheets("Closed - Other").Active
            B = Worksheets("Closed - Other").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("Closed - Other").Cells(B + 1, 1).Select
            ActiveSheet.Paste
            Worksheets("Open Tasks").Active
        End If

    Next

    For i = 2 To A
        If Worksheets("Open Tasks").Cells(i, 7).Value = "Closed - OBE" Then
            Worksheets("Open Tasks").Rows(i).Cut
            Worksheets("Closed - Other").Active
            B = Worksheets("Closed - Other").Cells(Rows.Count, 1).End(xlUp).Row
            Worksheets("Closed - Other").Cells(B + 1, 1).Select
            ActiveSheet.Paste
            Worksheets("Open Tasks").Active
        End If

    Next
End Sub

The above code will pull single rows, but when I have multiple rows that are merged and meet the conditions, I get an error:

"Run-Time Error "1004"
We Can't Do That To A Merged Cell  

Primary Work Space/Sheet

• excel
• vba

Share Improve this question Follow edited Nov 22, 2024 at 6:09 Siddharth Rout 149k1818 gold badges209209 silver badges256256 bronze badges asked Nov 22, 2024 at 2:55 Jared TurnerJared Turner 1122 bronze badges 2

• The SelectionChange event is triggered whenever the selection on a sheet changes (e.g., when a user selects a cell). Are you sure you want the script to run with every selection change? – taller Commented Nov 22, 2024 at 4:37
• 1 I agree with @taller here. I would recommend using Worksheet_Change event instead. You can use Intersect to check if the change happened in the 7th column. You may want to see this post on how to work with Worksheet_Change. Also Avoid the use of .Select/.Activate. To handle merged cells, you can check if the cell (Say in column F) in the relevant row has merged cells? – Siddharth Rout Commented Nov 22, 2024 at 6:05

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

This is one approach to select all rows which merged in a cell.
Change the following lines (according to the actual commands in them) in all For...Next loops.
As on the screenshot can be seen, the surely merged cells are in column G, so the code test those cells.
The cycle counter also should be modified according to the merged range rows count.
In VBA Worksheet require Activate method.
When using the End method it will not detect the merged cells in column A.
Detect the last cell in column H, where are no merged cells at all.

 If Worksheets("Open Tasks").Cells(i, 7).Value = "Closed - Complete" Then
            Worksheets("Open Tasks").Cells(i, 6).MergeArea.EntireRow.Cut 'added
            i = i + Worksheets("Open Tasks").Cells(i, 6).MergeArea.Rows.Count    'added
            'Worksheets("Open Tasks").Rows(i).Cut     'commented out
            Worksheets("Closed - Complete").Activate
            B = Worksheets("Closed - Complete").Cells(Rows.Count, 8).End(xlUp).Row  '1 changed to 8
            Worksheets("Closed - Complete").Cells(B + 1, 1).Select
            ActiveSheet.Paste
            Worksheets("Open Tasks").Activate
        End If

本文标签： excelPull merged rows based on a condition to a new pageStack Overflow