VBA Cut to Range is Skipping 2nd Row

Question

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).

Try updating the code so that your `Range`, `Cell`, and `Rows` functions all are led with the sheet you want to reference; i.e., `Workbooks("Blank1.xlsx").Worksheets("Sheet1").Rows.count` instead of just `Rows.count` — Jaycal, Oct 30 '13 at 18:41
For your code to work column **A** must be populated in the source sheet. — Gary's Student, Oct 30 '13 at 19:00
Hi Jaycal, thanks for the response. Tried adding your suggestion, but still the same issue. 1st row gets cut, 2nd row gets skipped, then it cuts everything else successfully. — lance fallon, Oct 30 '13 at 22:11

score 2 · Accepted Answer · answered Oct 31 '13 at 15:38

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.

Wow, you're absolutely right! Very interesting. I had no idea CUT worked that way... I'd rep you, but don't have enough points. — lance fallon, Nov 01 '13 at 02:51
Me neither; that was my new thing learned for the day as well :) — Jaycal, Nov 01 '13 at 12:36

score 0 · Answer 2 · edited May 23 '17 at 12:06

0

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

edited May 23 '17 at 12:06

Community

1
1

answered Oct 30 '13 at 18:59

ARich

3,230
5
30
56

Sorry, I screwed up the code I initially posted. I am actually cutting, not copying (and i'm not deleting anything after the cut)... Also, in my actual macro I'm not cutting everything. There are a number of conditions to determine whether or not I want to cut the row. Problem is, it skips the 2nd row altogether (does no comparision & obviously will never cut the row).... In my example, I am cutting each row individually just for the sake of simplicity. – lance fallon Oct 30 '13 at 22:15
Also, I should add that iterating over 'i' does work. But I'm more just curious as to the behavior of CUTTING when I use the for each. Just seems bizarre that it works with COPY, but not with CUT – lance fallon Oct 30 '13 at 22:25
Interesting. Thanks for updating the code. Perhaps if you post more of your code it will give us some insight into why it's behaving this way? – ARich Oct 30 '13 at 22:49
Interesting indeed... I pulled this out of the code & have it in its own sub-routine and it's behaving exactly the same way (I excluded the rest of the code, to isolate the issue) – lance fallon Oct 30 '13 at 23:04

VBA Cut to Range is Skipping 2nd Row

2 Answers2