将行值复制到另一个行值之后，然后将整行复制到另一个工作表 [英] Copy row values after another row values, copy whole row to another sheet afterwards

问题描述

我是这个方面的新手。
尝试执行以下操作：我将数据配对成行，一个在另一个之下，并且我希望它与另一个成对（所以我希望它不是A2到FP2以及A3到FP3的范围A2至MF2）。因此，基本上，我需要将其他所有行都追加到上一行。

I am a total novice at this. Trying to the following: I have paired data in rows, one below the other, and I want it to be one next to the other (so instead of ranges A2 to FP2 and A3 to FP3, I want it to be A2 to MF2). So, basically, I need to append every other row to the previous row.

我一直试图制作一个循环来复制它，然后将该行剪切到另一行。工作表，以便条件保持不变（总是在第2行旁边复制第3行），但随后我无法将其复制到第二张工作表的新空闲行中。我在调试过程中遇到了各种问题（Fors，Ifs，Columns ...）

I've been trying to make a loop to copy it and then cut that row to another sheet, so that the condition stays the same (always copy row 3 next to row 2), but then I can't make it copy into new free row of second sheet. I have encountered various problems during debugging (Fors, Ifs, Columns...)

无论如何，这是当前的代码，尽管很糟糕，在此先要多谢！

Anyway, here is the current code, although awful, and thanks a lot in advance!

Sub pairs()

Application.CutCopyMode = False
For Each cell In Sheets("Sve").Range("A2")
        Sheets("Sve").Select
        Range("A3:FQ3").Select
        Selection.Cut
        Range("FR2").Select
        ActiveSheet.Paste
        Rows("3:3").Select
        Selection.Delete Shift:=xlUp
        Rows("2:2").Select
        Selection.Cut
        Sheets("Gotovo").Select
        sourceCol = 1
        rowCount = Cells(Rows.Count, sourceCol).End(x1Up).Row
        For currentRow = 1 To rowCount
        currentRowValue = Cells(currentRow, sourceCol).Value
            If IsEmpty(currentRowValue) Or currentRowValue = "" Then
                Cells(currentRow, sourceCol).Select
            End If
        Next
        ActiveSheet.Paste
        Sheets("Sve").Select
Next

End Sub

推荐答案

我发现，如果您从底部开始操作数据，那么在遍历数据时删除行会更容易。

I've found that it's easier to delete rows as you're looping through data if you start from the bottom and work your way up.

Dim myRange As Range
Dim rowCount As Integer

' Get the range of cells and the count of cells in the A Column
Set myRange = Range("A1", Range("A1").End(xlDown))
rowCount = myRange.Cells.Count

' Start loop at the last row and decrease by 2
For i = rowCount To 2 Step -2

    ' Get the data in the i-th row, inclusive of blank cells between data
    Set secondrow = Range(myRange.Cells(i, 1), Cells(i, Columns.Count).End(xlToLeft))

    ' place i-th row at the end of the (i-1)th row, inclusive of any 
    ' blank cells b/t data
    secondrow.Copy Cells(i - 1, Columns.Count).End(xlToLeft).Offset(0, 1)

    ' Delete the second row
    myRange.Cells(i, 1).EntireRow.Delete
Next i

现在您有了一个工作表，其中行的格式是您想要的格式，您可以更新代码以将所有这些数据复制到其他工作表中。

Now that you have a sheet with the rows formatted the way you want, you can update your code to copy all of this data over to your other sheet.

已更新

此代码包含一种用于捕获包含空白数据的行的方法。由于一行数据可能包含空白单元格（例如AB（空白）D），因此我使用 Columns.Count 来获取该行的最后一列，然后使用 .End（XlToLeft）方法获取该行的最后一个非空白单元格。粘贴数据时，您只需要将列号增加一（即 Offset（0,1）），以便不复制表格中最后一个数据单元格行。

this code includes a method for capturing rows with blank data. Since a row of data may have blank cells (e.g. A B (blank) D), I use Columns.Count to get the very last column of the row, and then use the .End(XlToLeft) method to get the last non blank cell of the row. When pasting the data, you simply have to increase the column number by one (i.e. Offset(0,1)) in order to not copy over the last cell of data in the row.

这篇关于将行值复制到另一个行值之后，然后将整行复制到另一个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！