CODEWITHSUNDEEP
vbaexcel
lance fallon
lance fallon

Reputation: 59

VBA Cut to Range is Skipping 2nd Row

For Each cell In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
cell.EntireRow.Cut Workbooks("Book2.xlsx").Worksheets("Sheet1").Cells(Rows.Count,1).End(xlUp).Offset(1, 0)
Next cell

Gotta quick question. This piece of code loops through a range, and cuts the entire row / pasting it onto another workbook/worksheet.

However, it cut/pastes the FIRST row successfully. It then skips the 2nd row. And continues after that as expected, cutting/pasting every row (in fact if i set a breakpoint, you can see it hit the first row, skip the 2nd, then run fine & hit all others (H2, H4, H5, etc.). If I change the method to "COPY", it runs fine (H2, H3, H4, etc.).

Note: I can use 'COPY' or some other workarounds. But really am just wondering why 'CUT' behaves this way.

UPDATE: Sorry, I've update the code to CUT instead of COPY. And yea, I understand that that I need a header row in the target sheet. My only question is why the 2nd row is never cut from the source sheet (But when I replace CUT with COPY it works just fine).

Upvotes: 1

Views: 1814

Answers (2)

Jaycal
Jaycal

Reputation: 2087

This really is an interesting one. I thought it may be the for loop that's causing the problem (i.e. Next cell), but in testing, that turned out to not be the issue either. I even dropped it into a do while loop and got the same result. Last resort (which should have been the first resort :) ), I went to the MDSN to see what they said about Cut; here is the key sentence

The cut range must be made up of adjacent cells.

While I think your code meets this condition, the "adjacent cell" statement got me thinking...Is it skipping because you're cutting the first row in the range, with no previous row/cell in the range? Going along those lines, i tried this code, which works, and still utilizes .Cut. 

Set myRange = Worksheets("Sheet1").Range("A1:A" & _
    Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, 1).End(xlUp).Row)
i = 2
Do While i <= myRange.Rows.Count
    myRange.Cells(i, 1).EntireRow.Cut _
        Worksheets("Sheet2").Cells(Worksheets("Sheet2"). _
             Rows.Count, 1).End(xlUp).Offset(1, 0)
    i = i + 1
Loop

I created a range that included one more row than your original, but started at the same point, which is now the second row of the range. Since this worked, this leads me to believe that when cutting, excel is using the previous row to set the pointer of the current row after the cut. Because there is no previous row when you cut the first row, it skips until it is able to do so, explaining why cutting the first row is not returning the desired results.

Upvotes: 2

ARich
ARich

Reputation: 3279

I don't see in your code where you're cutting the row, so I'm going to assume that you're also deleting the row after it's been copied. If this is the case, note that when you delete a row the rows below it shift up. This means that if cell.Row = 12 and you delete row 12, the next iteration will look at row 13 even though row 12 now has row 13's contents.

Normally to aviod this one iterates backwards through a loop (For i = 1 to 10 Step - 1).However, you can't iterate backwards through a For Each statement.

Try this instead:

For i = 2 to Cells(Rows.count, 1).End(xlUp).Row Step - 1  
    'Put your code here  
    'Tip: use Cells(i,"H") to reference each cell in column H
Next i

My question is why aren't you read/writing everything in that range at once? If every row in that range needs to be copied over, why not copy/paste everything at once? 

Dim Rng as Range  
Set Rng = Range("H2:N" & Cells(Rows.count, 1).End(xlUp).Row)  
'Note I changed the second column reference. Modify this to whichever is the last column in your range.  
Rng.copy Workbooks("Blank1.xlsx").Worksheets("Sheet1").Cells(Rows.count,
1).End(xlUp).Offset(1, 0)
Rng.Clear

Upvotes: 0

Related Questions