Uniquely identifying a row

Can anyone suggest how to uniquely identify a row in Excel spread
sheet. I am wishing to identify a row with some value or number. If
someone adds or delete a row then it shouldnt effect the rows. The new
row should have a new number, and the existing rows shouldnt be
adjusted according to addition or deletion.
Thanks

Name a cell in your row, in a column that won't get deleted

myrow = range("myName").row

If you are want to refer to particular cells in the row Name those.

You could programmatically hide the Name.
If the row is ever deleted the Name will return an error.

Regards,
Peter T


<[email protected]> wrote in message
news:[email protected]...
> Can anyone suggest how to uniquely identify a row in Excel spread
> sheet. I am wishing to identify a row with some value or number. If
> someone adds or delete a row then it shouldnt effect the rows. The new
> row should have a new number, and the existing rows shouldnt be
> adjusted according to addition or deletion.
> Thanks
>

Thanks Peter, but I am afraid I didn't quite understand that. Can you
elaborate please. Let me explain my problem again.
I am trying to uniquely identify a row: the purpose is to be able to
refere to a particular row(identified by some number or value). If
someone inserts a row, the new row should be assigned a unique id -
different from other rows. If a row is deleted the values shouldn't be
adjusted, and same goes with the addition of a row.
If I add a column, to fulfill that and populate with some
numbers(unique), then it should hold a unique value for a row. Now if I
add that column, and populate that with some formula it will not be
assigned to a row permenantly. With addition or deletion the value will
change. for example, if I assign "3" to row 3, and then insert a row,
row 3 will become row 4 and so is the value assigned to it.
Please help,
Thanks


Peter T wrote:
> Name a cell in your row, in a column that won't get deleted
>
> myrow = range("myName").row
>
> If you are want to refer to particular cells in the row Name those.
>
> You could programmatically hide the Name.
> If the row is ever deleted the Name will return an error.
>
> Regards,
> Peter T
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > Can anyone suggest how to uniquely identify a row in Excel spread
> > sheet. I am wishing to identify a row with some value or number. If
> > someone adds or delete a row then it shouldnt effect the rows. The new
> > row should have a new number, and the existing rows shouldnt be
> > adjusted according to addition or deletion.
> > Thanks
> >

I originally understood you wanted to return the row number of a particular
row which might move if user inserts or deletes rows below it. The Named
range will move so you can always return it's reference.

From what I now follow you want something that does this -

rows 1 - last row 10 numbered 1-10
user inserts 2 new rows in 6-7
new numbers 11 & 12 are inserted in A6 & A7 ?
If rows are deleted their old numbers in col-A are never used again

It's difficult to do this automatically as no Event is triggered when rows
are inserted/deleted. Could trap a click of the insert/delete menu items but
that's not foolproof. So it means activating any code manually or
semi-manually (eg next time sheet is activated).

However the chances are that user will select and deselect entire rows as a
prelude to insert/delete, so try this (should update when user makes next
selection).

' in normal module
Sub test()

RowNumbers ActiveSheet
End Sub

Sub test()
RowNumbers ActiveSheet
End Sub

Sub RowNumbers(ws As Worksheet)
Dim nLastRow As Long, n As Long
Dim rng As Range, cel As Range

With ws

With .UsedRange
nLastRow = .Rows(.Rows.Count).Row
End With
Set rng = .Range(.Cells(1, 1), .Cells(nLastRow, 1))

n = Application.Max(rng)
Application.EnableEvents = False
On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeBlanks)
End With

If Err.Number = 0 Then
On Error GoTo errH

If Not rng Is Nothing Then
For Each cel In rng
n = n + 1
cel.Value = n
Next
End If
End If
errH:
Application.EnableEvents = True
End Sub


' in Worksheet module (rt-click sheet tab > view code)

Dim nb As Byte

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If nb Then
nb = nb - 1
RowNumbers Me
ElseIf Target.Areas(1).Columns.Count = Me.Columns.Count Then
nb = 2
End If
End Sub

I haven't looked at what you mentioned about columns but perhaps something
along similar lines.

Regards,
Peter T


<[email protected]> wrote in message
news:[email protected]...
> Thanks Peter, but I am afraid I didn't quite understand that. Can you
> elaborate please. Let me explain my problem again.
> I am trying to uniquely identify a row: the purpose is to be able to
> refere to a particular row(identified by some number or value). If
> someone inserts a row, the new row should be assigned a unique id -
> different from other rows. If a row is deleted the values shouldn't be
> adjusted, and same goes with the addition of a row.
> If I add a column, to fulfill that and populate with some
> numbers(unique), then it should hold a unique value for a row. Now if I
> add that column, and populate that with some formula it will not be
> assigned to a row permenantly. With addition or deletion the value will
> change. for example, if I assign "3" to row 3, and then insert a row,
> row 3 will become row 4 and so is the value assigned to it.
> Please help,
> Thanks
>
>
> Peter T wrote:
> > Name a cell in your row, in a column that won't get deleted
> >
> > myrow = range("myName").row
> >
> > If you are want to refer to particular cells in the row Name those.
> >
> > You could programmatically hide the Name.
> > If the row is ever deleted the Name will return an error.
> >
> > Regards,
> > Peter T
> >
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > Can anyone suggest how to uniquely identify a row in Excel spread
> > > sheet. I am wishing to identify a row with some value or number. If
> > > someone adds or delete a row then it shouldnt effect the rows. The new
> > > row should have a new number, and the existing rows shouldnt be
> > > adjusted according to addition or deletion.
> > > Thanks
> > >
>