Excel vba - xlDown

Question

The code below tries to insert the selected range (passed as rng ) at the end of the worksheet. It works if there are already two lines (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 

So, if A1 and A2 are present, and if you call this method 100 times, it inserts 100 lines after them. But, if there are no lines or only A1, it just overwrites A2. I could see excel writing on the same line (overwrite)

It seems like something has to do with how xlDown calculates if there are less than 2 lines, not sure.

Any help would be appreciated.

Answer 1

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

End (xlDown) - VBA equivalent by pressing Ctrl + Down .

Try Ctrl + Down with

• empty column
• column with a value in row 1 but not another
• values ​​in lines 1 and 2
• the values ​​in lines 1, 2, 3, 7, 8, 9, 13, 14 and 15

This will give you an idea of ​​all the different lines, Ctrl + Down may keep you busy.

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

I am surprised that Set newRange = ws.Range("A1").End(xlDown).Offset(1, 0) worked with an empty column. Range("A1").End(xlDown) will bring you to the bottom line of the sheet, then .Offset(1, 0) will try to get you out of 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 the value A1 matters, but no other cells matter, RowLast will be set to 1.
• If the number of cells in column A matters, RowLast will be set to the bottom row with the value.
• If you have a value in the last line, it will be ignored.
• If you have a value in the last two lines, RowLast will be set to Rows.Count - 1.

I assume that you have no values ​​in the lines of the borm. If you don't care if row 1 is left empty 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 the ws sheet.

If you don't care that line 1 is left blank, the experiment with Ctrl + Down and Ctrl + Up will give you an idea of ​​the effect of various combinations of values.

Answer 2

The problem is that the Offset line points to a line that is not part of the range. But without bias, you end up in the actual last row of the value. Therefore, instead of trying to go down from the top, release from below.

Go End (Down) twice: once at the end of this range, again to exit this range. Then go to End (Up) once, and then shift it by 1:

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

Answer 3

I like Tony Dallimore's answer, but it still causes problems if the autofilter is on. I'm still looking for a soul other than the foreach loop.