VBA, empty cell is at last row

0
votes

In a VBA Excel macro, I copy rows from another sheet, select a different sheet, and try to find the next open cell before pasting them.

Range("A1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues

The page has a first row of column titles, and find then passes over these to the very last row???

https://ibb.co/kjdWi0

If dummy data

is put in the second row, or if there is data already present,

it works.

WHY is it doing this?

2
excelvba

2 Answers

2
votes

Range.End(xlDown) is equivalent to pressing Ctrl + ↓.

Test it on your own - if you only have a header in Row 1, then you'll jump to the last row in the Worksheet. If you have data below the header, you'll move to that row.

Use Range.End(xlUp) to find the next available row by moving up from the last row on the Worksheet - something like this:

With Sheet1
    .Cells(.Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End With
0
votes

standing your data layout, you can use:

Cells(WorksheetFunction.CountA(Columns(1)) + 1, 1).PasteSpecial Paste:=xlPasteValues

or, using explicit worksheet references (recommended):

With Worksheets("mySheetName")
    .Cells(WorksheetFunction.CountA(.Columns(1)) + 1, 1).PasteSpecial Paste:=xlPasteValues
End With