Can a VBA macro remember the last row it checked the last time it was run?

[s3a]

Can a VBA macro remember the last row it checked the last time it was run assuming the file is always used on a different computer? I ask because I need to add data from the top to the bottom of a certain Excel file and store it into another and, it must append the new data from the first file to the second one every day but given that there are a huge amount of rows, the loop would take very long to check all the previous rows it added to not re-add them so it would be very useful if I can just make the macro simply continue from where it was.

Any help in figuring out the syntax required for doing this, if it is possible, would be very much appreciated!

[amitdi]

I do not know the exact answer to your question but I can suggest a workaround. Create the sheet called "Info" where you can log the last record processed, timestamp, etc before your macro run ends. When the macro is called again, the first thing it will do is fetch the same data from "Info" sheet and run based on the data.

[JBeaucaire]

It sounds like you're updating from one workbook with many rows of data to be "processed" and sending some/all of the data to another workbook via macro.

I would suggest adding having your macro add a "flag" in the first workbook after it has "processed" a row to permanently mark that row so it is will be ignored in the future runs.

Also, if you have VBA access to the 2nd workbook, it's quite simple to see where the next empty row is and add data into it. If you wish some specific suggestions, post your code and let's look at it together. Don't forget to put code tags around the code. (see Forum Rules above, or example in in my signature)

[s3a]

JBeaucaire, I am interpreting the data from one workbook/file and writing stuff based on it to the other workbook/file. I cannot post the code but I think just knowing the syntax (or whatever it is and is called) for flagging would be enough so, could you please tell me it?

amitdi, I do like your workaround which might be pretty elegant given that both files only use one worksheet each but, I'd like to first see if the flagging that JBeaucaire mentioned would be better just in case these files end up using more than one worksheet in the future.

[JBeaucaire]

Assuming the data is in columns A:M and column N is empty, your macro can:

1) test how many rows of data there are and loop through the rows one at a time
2) first test is in column N, only process rows where that row is empty in column N
3) after processing a row, the macro marks that row in column, then loops to the next row
4) When the macro is done, the workbook is saved to insure these flags are saved.

Basic syntax for all that:

Please Login or Register  to view this content.