Inserts numeric random unique ID in a column in sheet, specify column, sheet, workbook, length from and to.
Needs GetRandom
Sub RandomUniqueID_Sheet(ID_Column, Optional ID_Sheet = "Active", Optional ID_Wb = "This", Optional ID_MinLen = 4, Optional ID_MaxLen = 8)
' Uses GetRandom to inserts a new unique (not repeated) number in a column
'
If ID_Wb = "This" Then ID_Wb = ThisWorkbook.Name
If ID_Sheet = "Active" Then ID_Sheet = Workbooks(ID_Wb).ActiveSheet
Do
NewID = GetRandom(ID_MinLen, ID_MaxLen, 1, 0, 0, 0, "")
If WorksheetFunction.CountIf(Workbooks(ID_Wb).Worksheets(ID_Sheet).Range(ID_Column & 1).EntireColumn, NewID) = 0 Then Exit Do
Loop
NewRow = Workbooks(ID_Wb).Worksheets(ID_Sheet).Range(ID_Column & Rows.Count).End(xlUp).Row + 1
Workbooks(ID_Wb).Worksheets(ID_Sheet).Range(ID_Column & NewRow).Value = NewID
End Sub
' Uses GetRandom to inserts a new unique (not repeated) number in a column
'
If ID_Wb = "This" Then ID_Wb = ThisWorkbook.Name
If ID_Sheet = "Active" Then ID_Sheet = Workbooks(ID_Wb).ActiveSheet
Do
NewID = GetRandom(ID_MinLen, ID_MaxLen, 1, 0, 0, 0, "")
If WorksheetFunction.CountIf(Workbooks(ID_Wb).Worksheets(ID_Sheet).Range(ID_Column & 1).EntireColumn, NewID) = 0 Then Exit Do
Loop
NewRow = Workbooks(ID_Wb).Worksheets(ID_Sheet).Range(ID_Column & Rows.Count).End(xlUp).Row + 1
Workbooks(ID_Wb).Worksheets(ID_Sheet).Range(ID_Column & NewRow).Value = NewID
End Sub
ID_Column, Optional ID_Sheet = "Active", Optional ID_Wb = "This", Optional ID_MinLen = 4, Optional ID_MaxLen = 8
RandomUniqueID_Sheet "C", "Sheet1"
Views 414
Downloads 150
CodeID
DB ID