Friday, November 12th, 2010 | Author:

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
Category: Excel
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

11 Responses

  1. 1
    Gene Martin 

    Thanks for the Max_Each_Column code (and Macro lesson.)

  2. 2
    Chris 

    I really like this macro, however, it doesnt preserve the value of the ID cell when the spreadsheet is resorted…

  3. 3
    Chad 

    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?

  4. 4
    Marc 

    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!

  5. 5
    Carl 

    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!

  6. 6
    Blain 

    Hi Carl,
    What problem are you having exactly? Maybe I can help.

  7. 7
    Chris 

    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

  8. 8
    James 

    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?

  9. 9
    Blain 

    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.

  10. 10
    Blain 

    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!

  1. […] is thanks to: YouTube Adding an ID field to an excel worksheet | lylt.org thanks […]

Leave a Reply » Log in