I follow what you're doing but 10k comments!!! Surely client would accept a
helper column, very significantly less file size in an already big one.

Lightly tested, but seems to work -

Populate a helper column with row numbers as far down as necessary, in this
eg col-A

Sub setup()
For i = 1 To 58
Cells(i, 1) = i
Cells(i, 1).Name = Chr(39) & ActiveSheet.Name & "'!rowID" & i
Cells(i, 2) = Chr(i + 64)
Next
End Sub

' in the worksheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim bApos As Boolean
Dim i As Long
Dim lastRow As Long
Dim nMid As Long
Dim x As Long
Dim sPrefix As String
Dim rng As Range
Dim ar As Range
Dim nm As Name

On Error Resume Next
With Me.UsedRange
lastRow = .Rows.Count + .Rows(1).Row - 1
End With

x = Application.Max(Columns("A"))
sPrefix = Chr(39) & Me.Name & "'!rowID"
nMid = Len(sPrefix) + 1
For Each ar In Target.Areas
With ar
For i = .Rows(1).Row To .Rows(1).Row + .Rows.Count - 1
If i lastRow Then Exit For
With Cells(i, 1)
Set nm = .Name
If nm Is Nothing Then
x = x + 1
.Name = sPrefix & x
.Value = x
Else
If Not bApos Then
If InStr(1, nm.Name, "'") = 0 Then nMid = nMid - 2
bApos = True
End If
If .Value < Mid(nm.Name, nMid, 5) Then
.Value = Val(Mid(nm.Name, nMid, 5))
End If
Set nm = Nothing
End If
End With
Next
End With
Next
End Sub

Try inserting rows, copy paste rows etc

Regards,
Peter T

"Steve Barnett" wrote in message
...
Think I'm there.

I initialise the spreadsheet by going down every row and creating a range
name (on a single cell in the row). I also save the range name in the cell
comment, so they're both the same.

When they are ready to submit the spreadsheet to me for processing, they
run
an update process that goes down each row and checks to see whether the
range identified by the cell comment matches the current cell address. If
it
does, everything is great and this is the same (logical)row that it was
before. It copes with the user inserting and deleting rows because I'm
using
a named range, which moves up and down accordingly.

If the user inserts a new row and then copies an existing row in to it,
the
new row will not have a range name and the cell address referenced by the
cell comment will not match the current cell, so I know this is a new row
and I can generate a new unique identifier, a new range name and a new
cell
comment.

I'm about half way through the code and it seems to be holding water so
far.
It's proving easier to code than to explain, I'm afraid.

Thanks for all the help.
Steve



"Peter T" <peter_t@discussions wrote in message
...
The "values" I suggested adding won't change. To initialise put the row
number as a value in each cell of a (hidden) column. In future events if
there is no value the relative cell maybe something like -

thisrowIDcellInColA = Application.Max(Columns("A")) + 1

But would need to figure something if user copy/pastes the entire row.

Regards,
Peter T

"Steve Barnett" wrote in message
...
I also need to "keep" the unique identifier once it's been set.

The purpose behind this is that I need to copy some data from the
spreadsheet in to a database. There is nothing in the rows of the
spreadsheet that "uniquely" identifies it so there is nothing that I
can
hold on to that makes the connection between the row in the spreadsheet
and
the record in the database.

Theory said that, if I could put a unique identifier in each row and,
once
set, that identifier didn't change (so I can't use row number) then I
had
something I could make the connection with.

I wonder if I could fiddle it with named ranges? Wonder if this would
work... Initially give a cell in every row a "name" (Say row-nnnnn).
Then,
when the user inserts rows and copies and pastes stuff around, the
named
range shouldn't change - it'll stick with the original cell. Then, when
I
close the spreadsheet, I scan down the column with the named ranges,
checking to make sure that every row has a range name (if that's
possible).
If I find a cell without a name, I add one.

Must go and play...

Thanks
Steve




"Peter T" <peter_t@discussions wrote in message
...
Afraid my suggestion about storing values etc doesn't cater for
possibility
of entire row being copied ):-

Peter T

"Peter T" <peter_t@discussions wrote in message
...
Hi Steve,

To cover all the scenarios you mention might be impractical, but for
what
purpose/usage do you need unique row identifiers, in addition to the
fact
you can always read row numbers.

Maybe you could define worksheet level named ranges, these would
move
as
you
insert rows though would end up with meaningless names for deleted
ranges
(#REF). Would you want 10k names though (but much better than 10k
comments).

If you were "allowed" to insert an extra column (hidden perhaps)
populate
with row numbers as values. Store the highest number somewhere (a
cell
or
named formula). To cater for row inserts & new rows at the end, in
selection
and/or change events check the identifier cell has a value. If not
increment
the stored highest number and place same as the new identifier.

But with the limitations imposed by your client you are a bit
strapped!

Regards,
Peter T

"Steve Barnett" wrote in message
...
I have a spreadsheet (Excel 2000) that contains around 10,000
rows.
I
now
need to be able to identify each row via a unique identifier, but
am
having
trouble working out what to do when new rows are added or when
existing
rows
are copied and paste.

New rows and copied rows should get new unique identifiers, but I
can
see
no
way of doing this. Does anyone have any suggestions? There is
nothing
unique
about the data that I can hang on to and the "SheetChanged" event
does
not
fire for inserted rows in Excel 2000.

This spreadsheet is owned by one of our clients, so I can't add
new
worksheets or columns to the existing workbook - I can just add
cell
comments (unless you have a better suggestion).

Can anyone help?

Thanks
Steve