Auto Generate ID in Excel

**swadson** · 09-05-2012, 02:05 PM

Hi,

I have an Excel where in Column B, I populate the an id like DG0001, DG0002...

First - I need to check for the existing records if the DG number is not assigned then the new DG number need to be assigned depending on the latest/maximum number
Now going forward, when an new row is added, the id needs to pick up the last DG number and increment it by 1 and assign an new id to that row. I am new to macros and would need the entire code. Please help.

Thanks!

**MaczaQ** · 09-05-2012, 03:25 PM

hello,

I've just created following code for You. I'm sorry without comments

Please Login or Register  to view this content.

In attached file You will find working examle (code in Module1)

**Tinbendr** · 09-05-2012, 04:13 PM

Another way.

Go to Office button ->Prepare -> Properties -> add 541 to the Author field.

This way, if the count gets off, you can easily go back and fix it.

Each time you run this macro, it grabs the value in author, adds one to it, searches for it, and the puts it in next row.

I could add some auto-increment if you like, but since the records are disorganized and random, I think human intervention is warranted.

Please Login or Register  to view this content.

**MaczaQ** · 09-05-2012, 04:18 PM

@Tinbendr
but max value of ID is "DG0615" before I tested your code

**Tinbendr** · 09-05-2012, 05:30 PM

Yeah, whatever the 'last record number' is. (Remember, I said human intervention.

**swadson** · 09-06-2012, 02:39 PM

Hi Tinbendr - Actually it would be great if you can add the auto-incement depending on when data to the new row is added, instead of pressing the "Press Me!" button that MaczaQ has give. Also, for the already existing rows, could an id be populated when we open the excel?

Thanks for you help!!

**Tinbendr** · 09-10-2012, 05:43 PM

Sorry for the delay. Been sick, then, playing catchup.

Originally Posted by swadson

Hi Tinbendr - Actually it would be great if you can add the auto-incement depending on when data to the new row is added,

Because there are trailing rows after the ID number, I feel that automatically auto-incrementing will be difficult to manage.

instead of pressing the "Press Me!" button that MaczaQ has give.

You could move the button to the ribbon.

Also, for the already existing rows, could an id be populated when we open the excel?

I don't understand this.

Thanks for you help!!

**Charri54** · 09-16-2016, 02:25 PM

This code worked well for me as an add new row and populating one cell with an auto ID number when my data is not filtered

However, I found a problem.... When my data is filtered on the sheet and I attempt to add new, it is populating data (all cells) from an existing entry....it doesn't seem to be a duplicate of that data either. so its changing the existing data ID number to new.. any clues to fix this? filtering by a location then adding a new row is required for my users....

thanks so much for this service!!

Cathy