How to paste the selected range (passed as rng) to the end of the worksheet?

Question

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.

Answer 1

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.