I'm currently have and excel speadsheet that has 3 rows. What I would like to do is create a macro that reads the last populated row, usually will be column A1, and then created a new column listing the current date and time.

So for example, I have

ColA   ColB   ColC
House   w      f
Car     d      f
Bike    f      f
Monkey  f

But would like to end up with

Name   Key   Location   Date
House   w      f       6/30/2015 9:49:05 PM
Car     d      f       6/30/2015 9:49:05 PM
Bike    f      f       6/30/2015 9:49:05 PM
Monkey  f              6/30/2015 9:49:05 PM

I'm not a VBA programmer, so this simple task is kicking my butt a bit but so far I have:

Sub LastRowInOneColumn()
        Dim LastRow As Long
        With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Columns(2).Insert
        Range(LastRow) = DateTime.Now 'Trying to tell it to use the number from LastRow  to add the date that many times.
        End With
        MsgBox LastRow 'Using to check that it's counting correctly. It is.'
    End Sub

Which is giving me an error when I try to pass "LastRow".

Recommended Answers

All 2 Replies

you don't need . before Cells or before row, also my suggestion to you is to do it more robust by automatically detecting which is the last used column and just populate the one after that one with the current time

If you always want to have it at column 4 just make a loop

for i to numberOfRows
    Cells(i,4) = the time now ...

If you can't figure it out let me know I can do it when home later ..

Thanks for input and the offer to help Slavi. Was able to get this working as needed.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.