VBA Code:
' VBA Code to Generate Random Unique IDs in Excel
Sub GenerateRandomUniqueIDs()
Dim ws As Worksheet
Dim rng As Range
Dim numRows As Long
Dim numDigits As Integer
Dim uniqueIDs As Collection
Dim newID As String
Dim i As Long
' Set the worksheet and range to generate IDs
Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace with the name of your worksheet
Set rng = ws.Range("A1:A10") ' Replace with the range where you want to generate the IDs
' Set the number of rows and number of digits in each ID
numRows = rng.Rows.Count
numDigits = 6 ' Adjust as needed
' Clear the existing IDs in the range
rng.ClearContents
' Create a collection to store unique IDs
Set uniqueIDs = New Collection
' Generate and assign unique IDs to the range
For i = 1 To numRows
newID = GenerateRandomID(numDigits)
While Not IsUniqueID(uniqueIDs, newID)
newID = GenerateRandomID(numDigits)
Wend
rng.Cells(i).Value = newID
uniqueIDs.Add newID
Next i
' Display a message
MsgBox "Random unique IDs have been generated."
End Sub
Function GenerateRandomID(numDigits As Integer) As String
Dim i As Integer
Dim newID As String
Randomize ' Initialize the random number generator
' Generate a random ID with the specified number of digits
For i = 1 To numDigits
newID = newID & CStr(Int((9 - 0 + 1) * Rnd + 0))
Next i
GenerateRandomID = newID
End Function
Function IsUniqueID(uniqueIDs As Collection, newID As String) As Boolean
On Error Resume Next
uniqueIDs.Add newID, CStr(newID) ' Try adding the new ID to the collection
IsUniqueID = (Err.Number <> 457) ' Check if there was an error (457 indicates duplicate key)
On Error GoTo 0
End Function
Join Our WhatsApp Group
Nazim Khan (Author) 📞 +91 9536250020
[MBA in Finance]
Nazim Khan is an expert in Microsoft Excel. He teaches people how to use it better. He has been doing this for more than ten years. He is running this website (TechGuruPlus.com) and a YouTube channel called "Business Excel" since 2016. He shares useful tips from his own experiences to help others improve their Excel skills and careers.
