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
        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".

Edited by SpeakerKeyboard

3 Years
Discussion Span
Last Post by SpeakerKeyboard

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 ..

Edited by Slavi

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.