- Apr 10, 2017
- Office Version
Hi, I use this code to number item in my database. Now I want to take it to the next level; instead of always looking for max and add 1, this time look for sequence. Example for 1, 2, 3, 5. Since 4 is missing, the next item into the database used be indexed 4. Then say I have only 3, 4, 6 in the database, then the numbering for new entries should be 1, 2, 5. The idea is, until those gaps are filled, reserve the max plus 1 rule.
With Sheets("DATA") Set Drng = .[A2] Set lrRng = .Cells(.Rows.Count, Drng.Column).End(xlUp).Offset(1, 0) lrRng = Application.Max(.Range(Drng, lrRng.Offset(-1, 0))) + 1 End With