Hi, first time using this forum. So apologies if I don't know all the rules.
I'm working on an excel spreadsheet. And would like to implement a unique ID column (like access has). I found some via code and compiled them to suit my need. But I hit a roadblock.
Here's what I'd like to do. I have lots of rows with contact info, and I want to add a ID column, now till now I just typed in 1001 on the first row. And filled it down. The only thing is that I constantly copy in filled in rows from another sheet. And since my rows aren't sorted consecutively... I can't just fill in an ID for the newly pasted rows, since I'm not sure if the last highest number used...
So I have this code (below) that runs through my selection and fills in the blank cells with the highest number found in the ID column + 1... the only thing is that if it fills in all blanks with that same newly calculated value. Instead of each blank row with a consecutive number. I guess all I'm trying figure out is how to modify this code to recalculate and rerun the function 1 cell at a time (I guess from the top down).
If anyone can help me out I'd greatly appreciate it. perhaps I've been doing this wrong all along.
Here's the code that I have now:
(See attached pic for results)HTML Code:
Sub FillEmptyBlankCellWithValue()
Dim cell As Range
Dim InputValue As String
On Error Resume Next
InputValue = Application.WorksheetFunction.Max(Columns("A")) + 1
For Each cell In Selection
If IsEmpty(cell) Then
cell.Value = InputValue
End If
Next
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
Bookmarks