I have a table in Excel containing data, where users can either add new data or change existing data. When they do, I need to initiate a macro to process the data. I can initiate the macro by watching the table using the Worksheet_Change(ByVal Target As Range) method.
The issue I'm facing is that the table can have many thousands of rows. However, I only need to process the data on the row(s) that changed. So, I want to know which row(s) or cell(s) changed. Is there a way to determine this?
Private Sub Worksheet_Change(ByVal Target As Range)
Const csWatchRange As String = "N4:Q32768"
Dim rngWatchRange As Range
Set rngWatchRange = Me.Range(csWatchRange)
If Not Intersect(rngWatchRange, Target) Is Nothing Then
'Do something
End If
End Sub
It's generally good practice to use constants for unchanging values like, in this case, the range you're watching. It makes maintenance a lot easier, and allows you to reference the locations you're using multiple times without repeating yourself.
Assigning your ranges to variables also makes the code easier to read and maintain.
In this case, Target refers to all the cells being changed. Intersect will pick up any change that affects any cell in the chosen range. I would suggest that you assign Target to its own variable if the code fires, thus:
If Not Intersect(rngWatchRange, Target) Is Nothing Then
set rngChangeList = Target
End If
However, it might be easier and more robust if rather than using a specific range of cells and Intersect, you could use a cell property instead. I like to use the cell's Locked property, since that will also work if the worksheet is protected to prevent users from changing the wrong values - though whether that works for you or not is on a case-by-case basis, of course. So I would use:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Locked Then
'do something
End If
End Sub
You could also use properties like number format, background colour, comments, or dependencies - whatever works for you. I use Locked because users very rarely change it, or even know it exists.
Final thought, going back to Intersect - you said that the data is "In a table". If the data range is formatted as an Excel table, you could use that table's name in place of the range address, again making your code more robust.
Private Sub Worksheet_Change(ByVal Target As Range)
Const csWatchRange As String = "DataTable"
Dim rngWatchRange As Range
Set rngWatchRange = Me.ListObjects(csWatchRange).Range
If Not Intersect(rngWatchRange, Target) Is Nothing Then
'Do something
End If
End Sub