Copy row values after another row values, copy whole row to another sheet afterwards

Question

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.

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

Jaycal · Accepted Answer · 2013-11-24T05:35:52.530

0

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.

UPDATED

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.

edited Nov 24 '13 at 05:35

answered Nov 20 '13 at 16:36

Jaycal

2,087
1
13
21

Um, maybe i don't understand correctly what you did, but I don't want my every other row to be deleted - I want it copied after data in the previous row. And I don't get that when I use this code. I am sorry if I'm doing something wrong... – user3013123 Nov 20 '13 at 17:16
Your sample code has you deleting the third row after it is copied to the second row..? – Jaycal Nov 20 '13 at 18:21
Yes, but keep in mind that at that point that row is entirely empty, because the data from it has been copied. – user3013123 Nov 22 '13 at 18:47
The solution I posted will take every even row and append it to the end of the preceding row. The delete of the second row comes after the data is moved, so, for example, if you have 8 rows of data containing AA to HH, this code will result in 4 rows of data with AABB, CCDD, EEFF, GGHH. Am I missing something? – Jaycal Nov 22 '13 at 19:24
Probably not, the issue is on my side, I'd vager. – user3013123 Nov 23 '13 at 21:07
When I run the code, I don't get the data appended because there are empty cells in rows (missing data). I suppose that's the problem. Sorry for the trouble, I'll just try organize it differently... Maybe I will filter by a variable every second row, copy it to another sheet and then copy (append) it all together. Thanks for help and sorry for the inconvenience. – user3013123 Nov 23 '13 at 21:09
Not a problem, dealing with rows / columns will blank cells can be a bit tricky, but still somewhat manageable.. See the updated code. This will find the last populated cell in the row, including any blank cells that may be between them. – Jaycal Nov 24 '13 at 05:18

Copy row values after another row values, copy whole row to another sheet afterwards

1 Answers1

Linked