8

The code below is trying to paste the selected range (passed as rng) to the end of the worksheet.

It works if there are two rows already present (A1, A2).

Sub copyRow(rng As Range, ws As Worksheet)
    Dim newRange As Range
    Set newRange = ws.Range("A1").End(xlDown).Offset(1, 0)
    rng.Copy
    newRange.PasteSpecial (xlPasteAll)
End Sub

If A1 and A2 are present and you call this method 100 times, it inserts 100 rows after them.

If no rows are present or only A1, it just overwrites A2. I could see Excel write on the same row (overwrite).

Appears something to do with how xlDown calculates if there are less than 2 rows.

Community
  • 1
  • 1
bsr
  • 57,282
  • 86
  • 216
  • 316

1 Answers1

35

Sorry but I do not agree with Michael's answer.

End(xlDown) is the VBA equivalent of clicking Ctrl+Down.

Try Ctrl+Down with

  • an empty column
  • a column with a value in row 1 but no other
  • values in rows 1 and 2
  • values in rows 1, 2, 3, 7, 8, 9, 13, 14 and 15

This will give you an idea of all the different rows, Ctrl+Down might take you to.

Set newRange = ws.Range("A1").End(xlDown).End(xlDown).End(xlUp).Offset(1, 0) does not necessarily take you to the last used row plus 1.

I am surprised Set newRange = ws.Range("A1").End(xlDown).Offset(1, 0) worked with an empty column. Range("A1").End(xlDown) would take you to the bottom row of the sheet then .Offset(1, 0) would try to take you off the sheet.

Consider:

Dim RowLast As Long

RowLast = ws.Cells(Rows.Count, "A").End(xlUp).Row
  • If column A is empty, RowLast will be set to 1.
  • If A1 has a value but no other cells have values, RowLast will be set to 1.
  • If a number of cells in column A have values, RowLast will be set to the bottom row with a value.
  • If you have a value in the final row, it will be ignored.
  • If you have a value in the final two rows, RowLast will be set to Rows.Count - 1.

I assume you do not have values in the borrom rows. If you do not care if row 1 is left blank with an empty column, then:

RowLast = ws.Cells(Rows.Count, "A").End(xlUp).Row
Set NewRange = ws.Cells(RowLast + 1, "A")

should give the desired result regardless of the current contents of sheet ws.

If you do care about row 1 being left blank, experimenting with Ctrl+Down and Ctrl+Up will give you an understanding of the effect of different combinations of values.

Tony Dallimore
  • 12,335
  • 7
  • 32
  • 61
  • 5
    +1 for nicely explaining it. As it is, I have never been in favor of using "ws.Range("A1").End(xlDown)" to set a range. Reason being, It doesn't give (quite understandably) the correct range if there is a blank cell in between. :) – Siddharth Rout Feb 23 '12 at 03:34
  • Tony, thanks for ur help. It worked for me. About the first part, why I did it that way, I was following item 18 http://support.microsoft.com/kb/291308 . Surprised to see no easy way to do this. thanks aga. – bsr Feb 23 '12 at 03:44
  • @SiddharthRout .. or worse the last cell if the row is blank. Looking up is much better, but still a test is needed to ensure the data range of interest is not blank. [`Find` can be easier](http://stackoverflow.com/q/4872512/641067) depending on what is needed – brettdj Feb 24 '12 at 23:56
  • @ brettdj. I wondered about Find but it too has its problems. I discovered a year or two ago that `Find` will not recognise a merged cell. I think I know all the boundary conditions for `End` which is why it is my current favourite. I think I have listed all the things that can go wrong with `End(xlUp)` and have encouraged bsreekanth to experiment. I am not sure I could have done more. – Tony Dallimore Feb 25 '12 at 09:59
  • 1
    @bsreekanth. I will look at your link but it sounds as thought it is giving poor advice. There is, to my knowledge, no single reliable method of identifying the last row or column of a range. I agree with brettdj, `Find` is one of the more reliable methods but it too has its problems. `SpecialCells` is another good method by which I mean it has not failed me. I have tried to list all the ways `End(xlUp)` may not give you the result you expect. I hope I have made clear that you must test these boundary conditions. – Tony Dallimore Feb 25 '12 at 10:06
  • 1
    @Tony - note that End ignores hidden or filtered out rows or columns – Charles Williams May 29 '15 at 09:25
  • 1
    @CharlesWilliams. As I said earlier, no method of finding the last row/column works in all situations. I had not thought about hidden rows but it does not surprise me they cause problems. The chosen approach must be tested with the target worksheet to ensure it works as expected. – Tony Dallimore May 29 '15 at 20:32