Tracking newly entered rows.
 

Currently I have a sheet which contains data in columns A-Q, I have added
some code in WorkSheet_Change to track any changes in the existing data, this
same code is currently telling me that a newly entered row of data has been
changed which is technically correct but I'd rather have it tell me that the
row is a new row rather than changed. Code I'm using is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Integer
If Not Intersect(Target, Range("A:Q")) Is Nothing Then
iRow = Target.Row
Cells(iRow, 19).Value = "Changed!"
End If
End Sub

Currently this puts "Changed!" in column S, if any new code means using yet
another column that would be okay too. It would be cool if it remained in
the same column though.

Ideas or suggestions?

Thanks,

Larry

Tracking newly entered rows.
 

Larry,

One thing to try is to create a module-level variable -- or a
Public one if you want to use it elsewhere in your code -- and compare
the current value of it to the Target.row. If Target.row is higher,
it's a new row. Use the worksheet.activate event to initialize the
value (or workbook.open, particularly if you're using a public
variable) by setting it to the last used row on the sheet. Finding the
last used row is a whole separate topic because of how Excel
"remembers" things, but simplistically something like this should get
you started:
dLastCurrentRow = ActiveCell.SpecialCells(xlLastCell).Row

HTH
/ Tyla /



On Apr 17, 12:42 pm, Larry Cooke
wrote:
Currently I have a sheet which contains data in columns A-Q, I have added
some code in WorkSheet_Change to track any changes in the existing data, this
same code is currently telling me that a newly entered row of data has been
changed which is technically correct but I'd rather have it tell me that the
row is a new row rather than changed. Code I'm using is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Integer
If Not Intersect(Target, Range("A:Q")) Is Nothing Then
iRow = Target.Row
Cells(iRow, 19).Value = "Changed!"
End If
End Sub

Currently this puts "Changed!" in column S, if any new code means using yet
another column that would be okay too. It would be cool if it remained in
the same column though.

Ideas or suggestions?

Thanks,

Larry

Tracking newly entered rows.
 

Thanks Tyla, though I just thought of another twist in things. Sometimes
they will copy an entire row and insert it into the sheet and then modify the
different cells rather than inserting a blank row. This is getting more
complicated than I originally thought so I'm going to have to rethink the
whole concept now. I will keep your idea in mind, it's a good one just not
sure it's going to handle my situation now.

Thank you,

Larry

"Tyla" wrote:

Larry,

One thing to try is to create a module-level variable -- or a
Public one if you want to use it elsewhere in your code -- and compare
the current value of it to the Target.row. If Target.row is higher,
it's a new row. Use the worksheet.activate event to initialize the
value (or workbook.open, particularly if you're using a public
variable) by setting it to the last used row on the sheet. Finding the
last used row is a whole separate topic because of how Excel
"remembers" things, but simplistically something like this should get
you started:
dLastCurrentRow = ActiveCell.SpecialCells(xlLastCell).Row

HTH
/ Tyla /



On Apr 17, 12:42 pm, Larry Cooke
wrote:
Currently I have a sheet which contains data in columns A-Q, I have added
some code in WorkSheet_Change to track any changes in the existing data, this
same code is currently telling me that a newly entered row of data has been
changed which is technically correct but I'd rather have it tell me that the
row is a new row rather than changed. Code I'm using is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Integer
If Not Intersect(Target, Range("A:Q")) Is Nothing Then
iRow = Target.Row
Cells(iRow, 19).Value = "Changed!"
End If
End Sub

Currently this puts "Changed!" in column S, if any new code means using yet
another column that would be okay too. It would be cool if it remained in
the same column though.

Ideas or suggestions?

Thanks,

Larry

Tracking newly entered rows.
 

Larry,

Life would be so much easier without creative users -- but a lot
less fun.

/ Tyla /

On Apr 17, 2:04 pm, Larry Cooke
wrote:
ThanksTyla, though I just thought of another twist in things. Sometimes
they will copy an entire row and insert it into the sheet and then modify the
different cells rather than inserting a blank row. This is getting more
complicated than I originally thought so I'm going to have to rethink the
whole concept now. I will keep your idea in mind, it's a good one just not
sure it's going to handle my situation now.

Thank you,

Larry

"Tyla" wrote:
Larry,

One thing to try is to create a module-level variable -- or a
Public one if you want to use it elsewhere in your code -- and compare
the current value of it to the Target.row. If Target.row is higher,
it's a new row. Use the worksheet.activate event to initialize the
value (or workbook.open, particularly if you're using a public
variable) by setting it to the last used row on the sheet. Finding the
last used row is a whole separate topic because of howExcel
"remembers" things, but simplistically something like this should get
you started:
dLastCurrentRow = ActiveCell.SpecialCells(xlLastCell).Row

HTH
/Tyla/

On Apr 17, 12:42 pm, Larry Cooke
wrote:
Currently I have a sheet which contains data in columns A-Q, I have added
some code in WorkSheet_Change to track any changes in the existing data, this
same code is currently telling me that a newly entered row of data has been
changed which is technically correct but I'd rather have it tell me that the
row is a new row rather than changed. Code I'm using is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Integer
If Not Intersect(Target, Range("A:Q")) Is Nothing Then
iRow = Target.Row
Cells(iRow, 19).Value = "Changed!"
End If
End Sub

Currently this puts "Changed!" in column S, if any new code means using yet
another column that would be okay too. It would be cool if it remained in
the same column though.

Ideas or suggestions?

Thanks,

Larry