VBA to replicate the double-click-auto-fill

[vg05]

I'm trying to find a (hopefully simple, because I am a noob to VBA) code to replicate the auto-copy function that occurs when you double click the box in the bottom right corner of a cell.

Here is the long version: I am trying to select the first empty cell in column N - I have successfully done that with VBA. I am then trying to enter a time/date stamp into that cell - and I have successfully done that. Now I am trying to fill/copy that timestamp down to the end of the current data set. However, I need the range to be undefined as it will change often. So I cannot say For N1:N40 timestamp - because while it is 40 this time, next time it may be 85, or 130 after than.

I tried a Do While Loop, but I couldn't get that to work. Here is the code that I am currently using - should you modify it, would you mind explaining how/why as I am still trying to learn this?

Please Login or Register  to view this content.

So I am finding the empty cell in column N - adding a timestamp to that cell; so how do I copy that timestamp down to the end of an undefined data set? (It could copy in column N until column A is blank - but I'm not sure how to write that)

Thanks in advance!

[6StringJazzer]

It would help if you showed all your code.

Here is how to put date/time in the first empty cell in column N (assuming that there is no data in column N after the first empty cell) and then fill down to the row with the last value in column A.

This code assumes that either:

1) The code is in the module for the worksheet containing the data
or
2) The worksheet containing the data is the active worksheet.

If neither of these is true please explain how you will be using this code.

Please Login or Register  to view this content.

Also do not do this

Please Login or Register  to view this content.

Format creates a text string. You do not want to put a text string in the cell. Instead, format the cells in column N using Custom format to get the desired display.

[vg05]

That actually was ALL of my code for this module...I am trying to piece things together from creating macros/google/stack/other threads on here before creating my own thread - hoping to learn a little bit along the way. But I still don't even really understand the whole Dim/Long thing, and thats presumably why I am using that ActiveCell Format stuff - because I understand it.

But your code works perfectly, thank you for the help!

[6StringJazzer]

Glad it worked for you.

Dim is a statement to declare a variable. VBA does not require you do declare variables, but I strongly recommend to everyone that they use Option Explicit and declare variables. Doing so prevents a lot of bugs and runtime errors.

Each variable has a data type that is set by the Dim statement. Long means long integer. It can hold an integer value from -2,147,483,648 to 2,147,483,647.

If you do not use Option Explicit, and use a variable but do not declare it, it will automatically be declared as a Variant data type, which can hold any kind of data. That sounds like it would be good to use but it's actually terrible in most situations.

My code uses a Long variable to indicate the last row of data in columns A and N. The code I used takes advantage of the Excel model to do this in one operation. Once I have two lines I can assign Now to multiple cells in one operation.