I am new to VBA, I have a requirement of tracing all modified rows in excel. The excel file is expected to have millions of records out of that only few columns or rows are expected to be modified by user. To save time I plan to update only the modified records back to sql rest delete it. I managed to get the below code to do work as per the expectation but only if the user modifies manually. If the user copies and pastes multiple row only the first records is marked as modified rest is not.
My requirement to track anything that changes via manually or copy&paste in that workbook
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rw As Long
Dim cw As Long
rw = [Sheet1!b8].CurrentRegion.Rows.Count
cw = [Sheet1!b8].CurrentRegion.Columns.Count
lcw = ColumnLetter(cw)
Dim xrng As Range
Set xrng = Range("c1:" &lcw & rw)
If Not Application.Intersect(xrng, Range(Target.Address)) _
is Nothing Then
If Target.Row > 1 Then Cells(Target.Row, 1) = "M"
End If
End If
End Sub
Public Function ColumnLetter(ColumnNumber As Long) As String
ColumnLetter = Split(Cells(1, ColumnNumber).Address(True, False), "$")(0)
End Function
It is not entirely clean what you're gonna do with Intersect but here you are a snippet to mark all affected rows with an M in the 1st column:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim v
Application.EnableEvents = False
For Each v In Target
if v.Row > 1 And v.Column > 1 Then Cells(v.Row, 1) = "M"
Next
Application.EnableEvents = True
End Sub
Notes:
For Each loop manages multiple cells selection.
Application.EnableEvents = False is needed to prevent the sub from firing on changing Cells(..., 1)
This code is not perfect because it overwrites Cells(..., 1) as many times as many columns the affected range contains. But you can keep it this simple if it is not too slow. Give it a try.