Use of ' in offset

Question

I have some code, can someone explain how it works and why it works that way? Specifically this line of code:

With Sheets("Sheet2")
    .Cells(1, 1).End(xlDown).Offset(1).Value = Format(Date, "dd/mm/yyyy")

Now I get it that the code itself outputs the date in Cells(1,1) and offsets by 1 from the bottom for every subsequent entry. What I don't understand is why in the sheet itself one of the cells is blocked out?

And then the code works fine. But if cell A2 doesn't have '-------- it suddenly doesn't work anymore and gives me Application defined, object defined error. Can someone explain what '-------- does here and why its used?

score 2 · Answer 1 · answered Dec 09 '21 at 12:48

When there is no value in A2, it will give you an error because End(xlDown) from A1 will take you to the last cell A65536 in Excel 2003 and A1048576 in Excel 2007+. It is like selecting cell A1 and manually pressing the End key and then the Down arrow key. And of course you can't offset down from the last cell and hence you are getting the Error 1004 - Application-defined or Object-defined error.

I recommend using xlUp to find the last row and then enter the data as shown HERE

Here is an example

Dim lRow As Long

With Sheets("Sheet2")
    lRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1

    .Cells(lRow, 1).Value = Format(Date, "dd/mm/yyyy")
End With

score 2 · Accepted Answer · answered Dec 09 '21 at 12:48

The idea of the code is: "Jump to the last row that is used in column A, go down 1 row and write the date into it.

What it does: Go (virtually) to cell A1 and press (also virtually) the key Ctrl+Down. This jumps to the last used cell of a column - but only if there is more than one cell filled, else it will jump to the very last row of the sheet (try this in Excel to understand).

Now if you offset one row from the very last row of a sheet, Excel cannot do anything more than complain.

The solution: Use .Cells(.rows.count, 1).End(xlUp) instead. This works the opposite way: Go to the very last row and press Ctrl+Up. For more details how to find the last used cell, see Error in finding last used cell in Excel with VBA

What you also should do:
Don't cascade things, use intermediate variables, that helps debugging.
Don't write a date as string (format converts a date into a string). Write the date as date and set the number format of the cell.

With Sheets("Sheet2")
    Dim lastCell as Range
    Set lastCell = .Cells(.rows.count, 1).End(xlUp)
    lastCell.Offset(1).Value = Date
    ' If needed:
    ' lastCell.Offset(1).NumberFormat = "dd/mm/yyyy"
End With

Use of ' in offset

2 Answers2