Excel VB code : Code works only for the first row

Question

I have the following code :

' Paste last value
    Range(Cells(LastRowP + 1, 10), Cells(LastRowP + 1, 10)).Select
    Selection.Cut
    Range(Cells(LastRowP + 1, 9), Cells(LastRowP + 1, 9)).Select
    ActiveSheet.Paste

This code should cut the content from column 10 and paste it in column 9.Unfortunately this is working only for the first entry (row 2 for example) for the other rows, it doesn't work. Can you help?

Thank you!

LastRowP = Range("A65536").End(xlUp).Row ' Range("A65536").End(xlUp).Offset(1, 0).Select — adp, Oct 16 '13 at 12:15
the first entry in the `Range` indicates the start of the range. In your case both the start and the end of the range are the same cell (which is the last cell+1). — Ioannis, Oct 16 '13 at 12:21
@loannis: Your first comment actually answers the question ;) — Siddharth Rout, Oct 16 '13 at 12:45
@SiddharthRout It does, but with minimal learning for the OP and future visitors of this thread.. And it does not address that `Select` is a bad practice in this context (hence it is a comment :) ) — Ioannis, Oct 16 '13 at 13:00
@SiddharthRout Please post it back, I will delete the comment. Future visitors will benefit more from an elaborate explanation rather than from a quick comment. Deleting.. — Ioannis, Oct 16 '13 at 13:04
@loannis: No... Don't do that. It's not right for me to post the answer. I would seriously recommend you posting an answer with all points covered :) — Siddharth Rout, Oct 16 '13 at 13:06
@SiddharthRout Oh, I can do this only later (need to go now) so plz post back. Besides, thielemans's answer is good enough (+1) for the OP.. — Ioannis, Oct 16 '13 at 13:13
The problem is that my code is only taking the first row into consideration ,if for example I would write something something like:Range(Cells(LastRowP + 2, 10), Cells(LastRowP + 2, 10)).Select Selection.Cut Range(Cells(LastRowP + 2, 9), Cells(LastRowP + 2, 9)).Select ActiveSheet.Paste - > this would also take my second row into consideration ,is there any way I can put a range there ? so that I wont have to repeat this 20 times? I hope I'm preaty clear..this is my second week using VBA and coding overall ,so please bare with me:) — adp, Oct 16 '13 at 14:27

score 1 · Accepted Answer · answered Oct 16 '13 at 12:24

1

This should work:

Sub CutColumn()

   LastRow = Cells(Rows.Count, "J").End(xlUp).Row
   Range("J1:J" & LastRow).Cut Destination:=Range("I1:I" & LastRow)

End Sub

answered Oct 16 '13 at 12:24

CustomX

9,948
30
85
115

The problem with this is,that I have a title on the first row,and buy cuting paste, I cut the title from the first row,also I have several workbooks that are running this macros.After finishing to copy the first workbook,it looks great ,but when it moves on to the second and 3rd i have the problem that this code is cutting blank cells from the previous form and pasting it over the previous data. – adp Oct 16 '13 at 14:25
If you don't want to cut the title, change `J1 and I1` to `J2 and I2`. What do you mean by 2nd and 3rd is cutting blank cells? – CustomX Oct 17 '13 at 07:07

score 0 · Answer 2 · answered Oct 16 '13 at 12:26

0

You are not selecting the reange correct, and you haven't mentioned the value of the LastRowP. However, if you set the FirstRowP and the LastRowP their values in the following code will work

Range(Cells(FirstRowP + 1, 10), Cells(LastRowP + 1, 10)).Select
Selection.Cut
Range(Cells(FirstRowP + 1, 9), Cells(LastRowP + 1, 9)).Select
ActiveSheet.Paste

answered Oct 16 '13 at 12:26

Thanushan

532
2
8

3

[INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) – Siddharth Rout Oct 16 '13 at 12:35

score 0 · Answer 3 · edited May 23 '17 at 11:56

I think the existing answers should got you covered, but I would like to add some useful information that is relevant to your question.

First, the syntax Range(Cells(R1,C1), Cells(R2,C2)) refers to the square range that starts from the cell defined by the intersection of row number R1 and column number C1, till the cell defined by the intersection of row number R2 and column number C2 (assuming that R1 <= R2, else it is the other way round).

Therefore, the expressions Range(Cells(LastRowP + 2, 10), Cells(LastRowP + 2, 10)) and Cells(LastRowP+2,10) are identical: they both refer to the cell that lies in the intersection of row number LastRowP+2 (whatever that is) and column number 10. This is the reason why your code does not work as you expect.

Having said that, there are two important elements that are considered bad practice (and from your comment to t.thielemans anwer it seems that you already discovered one :) ).

Using Select

The macro recorder uses Select a lot, and it makes it easy for someone to dive into the word of macros.. however, the macro recorder has no idea of what your intention is, and tries to stupidly replicate any action.

What is wrong with Selections? First, a Selection returns whatever the user selects.. It can be a range, or a chart, or a sheet or whatever else... This makes it slow and error prone. The macro will fail if there is no selection, giving a run-time error. It is also slower, but for the majority of macros this is not a big issue.

In general it is not necessary to select ranges or activate worksheets in order to manipulate data. The only case I have found it necessary to activate a cell is when it is needed to programmatically add a comment to a cell. The link that Sid mentions above shows some good ways to manipulate data without selecting/activating stuff (objects).

Qualifying range objects

I assume that VBA developers rarely use syntax like

Range(Cells(FirstRowP + 1, 9), Cells(LastRowP + 1, 9))

I myself never use it.. This code is looking for trouble :)

Both Range and Cells (and also Columns or Rows etc ) are shortcuts. when the above code is placed in a VBA module, it is interpreted as the Range and Cells of whichever sheet happens to be activated at the moment. In other words, the compiler understands Range = ActiveSheet.Range and Cells = ActiveSheet.Cells. Code like Range("A1") will probably work "OK" when called from something attached to a worksheet (such as button), because the ActiveSheet will be the Sheet that we refer to.. It will still cause trouble if the macro activates another sheet and then tries to refer to the range of the initial sheet (that is why it will probably work "OK"). And it will cause errors when it is called from a shortcut key, a userform etc.

To add to the confusion, if the exact same piece of code is placed in a worksheet module, then Range and Cells are interpreted as belonging to that worksheet.. How cool is that? :)

In order to avoid such problems, I personally qualify all objects.. An exception is when the range is Named (check here for example), then we can refer to it just fine (unless we are in the case below!).

Now, the qualification subtleties carry over across workbooks.. So Sheet1.Range("A1") implies ActiveWorkbook.Sheet1.Range("A1"). If we activate a different workbook, Sheet1.Range("A1") will refer to the range of that workbook. If two named ranges of different workbooks have the same name, then we need to qualify them as well.

To wrap up, avoiding Selections and fully qualifying objects will save you from a lot of errors and trouble.

I hope this helps!

Excel VB code : Code works only for the first row

3 Answers3