Okay… This one has always bugged me. I like to have an ID or identity field on my rows even in spreadsheets just so that when I resort them someone can still say “Look at item #67” and I know what they are talking about.
Well I pieced together some stuff I found on the web (I think this was where I found the example of the max_Each_Column function) and this appears to do the trick. Very useful and I absolutely love it. It requires a macro enabled excel file, but that is the price we pay…
Here is a YouTube video of it…
Add this code to your worksheet and then modify it as needed to get the correct columns. As-is when you type in column b and then leave the cell it will add a value to the cell in column A. It looks in the column A, finds the max value and then adds one to it. If the ID already has a value then it leaves it alone, so editing rows doesn’t repopulate your ID field. Easy peasy right?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then 'This is the column that causes the ID to be created
'This uses the target offset so it assumes in two spots below that the column to update is "A" and
'that the column is one position to the left of the target column above
If Target.Offset(0, -1).Value = "" Then
Target.Offset(0, -1).Value = Application.WorksheetFunction.Max(Columns("A")) + 1
End If
End If
End Sub
Function Max_Each_Column(Data_Range As Range) As Integer
Dim TempArray() As Double, i As Long
If Data_Range Is Nothing Then Exit Function
With Data_Range
ReDim TempArray(1 To .Columns.Count)
For i = 1 To .Columns.Count
TempArray(i) = Application.Max(.Columns(i))
Next
End With
Max_Each_Column = TempArray
End Function
Thanks for the Max_Each_Column code (and Macro lesson.)
I really like this macro, however, it doesnt preserve the value of the ID cell when the spreadsheet is resorted…
Hi there. Thanks very much for this, as it helped me add this functionality to a project I’m working on. I just have a few comments.
First, one of the YouTube commenters provided a bug fix for a situation where you may want to delete data from multiple columns on a single row. His solution was to add an additional IF statement as seen below:
If Target.Columns.Count = 1 Then
If Target.Offset(0, -1).Value = “” Then
Target.Offset(0, -1).Value = Application.WorksheetFunction.Max(Columns(“A”)) + 1
End If
End If
While this fixes the error when deleting data from multiple columns, it doesn’t prevent an error from occurring when modifying data across multiple rows. To fix the error in these instances, I expanded upon the solution above by adding “And Target.Rows.Count = 1” to the additional IF statement, resulting in the following code:
If Target.Columns.Count = 1 And Target.Rows.Count = 1 Then
If Target.Offset(0, -1).Value = “” Then
Target.Offset(0, -1).Value = Application.WorksheetFunction.Max(Columns(“A”)) + 1
End If
End If
Unfortunately, this fix isn’t the best because it calculate unique IDs for new multi-row entries resulting from a copy/paste function. In these situations, the “target” rows and columns exceed a count of 1, so the rest of the calculations are ignored. I found a quick way to get them to calculate by simply “editing” the newly entered values in columns 2 by double clicking them and clicking away. This activates the cell and then saves the same data back to it, allowing the subroutine to run as intended. So it’s not a full fix, but at least it’s not throwing an error.
Second, you never actually call the “Max_Each_Column” function included in your post, so it can be left out. Perhaps it was just there as an example that led to your final solution?
Hi there thanks for this code works great however just a few questions as im still new to this sort of stuff!!
what do i edit to do make to following changes:
have the formula look at the value in column F to decide if it needs an ID letter in column A.
Change the ID ref from numerical to alphabetical.
have the number auto update if a value is deleted (from playing around it just keeps the value in the cell)
Thanks once again!
Hi,
This micro seems to be perfect for my project. Though, I have trouble running it or getting it to work. Just for test, I created the exact same sheet you have in your video and copied your code into my macro editor, but the macro doesn’t work. I dont know if I have to do anything to actually run the macro.
I am a very basic user, and starting to learn about simple macros. I am using Excel for Mac.
Thank you for the macro and your help!
Hi Carl,
What problem are you having exactly? Maybe I can help.
I love this code but I am having difficulty with locations. I want it to auto-id any row that has any value entered into column D. I changes the 2 to 4 and left the but it it putting the ID in column C…you can tell I cannot code – can you help?
/Coffee
Hey I love the micro. It is also perfect for what I am trying to do. However, I did have a question.
I want to change the automatic ID to say a text form.
Example:
“IT16-42” instead of “1”.
“IT16-43” instead of “2”
How would I do this?
Hi Chris, you need to adjust the two lines about the target offset…. so let’s say I want to put my ID into column B when column D data is adjusted….
Change the target.column=4 (this now specifies D as the one kicking off the change
then change the following:
If Target.Offset(0, -1).Value = “” Then
Target.Offset(0, -1).Value = Application.WorksheetFunction.Max(Columns(“A”)) + 1
End If
To
If Target.Offset(0, -2).Value = “” Then
Target.Offset(0, -2).Value = Application.WorksheetFunction.Max(Columns(“B”)) + 1
End If
So the target.offset is where it is in relation to the target we specified above (D) so we want to go -2 (left) from D to get “B” then you have to change the max function to look in column B to get the right value for the next iteration.
Hi James,
What I would recommend to simplify what you’re asking is to keep it populating the number as usual (otherwise the MAX function breaks that finds the next number for the ID)
So let that column exist (see my reply to Chris below about how to change which column it goes to) and then simply hide the column.
You can then use that in a function. So let’s say you make the ID appear in column “B” like I described for Chris. Hide column “B” and then make column A a function of B.
So make A1=CONCATENATE(“IT16-“,B1)
That should do it!
first the code is awesome, but I want to be able to “Archive” a row and have the count to continue and not re-use the same number. Can you help. Thanks.
Hi, i used the code, it definitely work well. But how do i make sure that i delete the number/ rows, the number will auto decrease also.
For example
11
12
13
so if i delete 12, 13 will be 12