excel - How can I determine which cell(s) in a watched range are changed? - Stack Overflow

admin管理员组

文章数量:1346033

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?

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?

• excel
• vba

Share Improve this question Follow edited 2 days ago BigBen 50.2k77 gold badges2828 silver badges4444 bronze badges asked 2 days ago EnantiomeerEnantiomeer 7111 silver badge99 bronze badges 8

• 6 Target is the cell(s) that changed. – BigBen Commented 2 days ago
• 4 ...but note that Target may also include cells outside of the range you're tracking, so use something like (eg) Dim rng as Range: Set rng = Application.Intersect(Target, Me.Range("A2:K100") where A2:K100 is the range of interest. Then work with rng and not Target – Tim Williams Commented 2 days ago
• Please add the code you've got to your question and share in more detail where your data is located and what (which columns) you're going to process and how. – VBasic2008 Commented 2 days ago
• @BigBen Let me check if I understand correctly. Right now I'm using the following code to check if anything in my watched range changed: If Not Application.Intersect(Range("N4:Q32768"), Range(Target.Address)) Is Nothing Then Then, since Target is the cell that changed, I could use Target.Row to find out the row number of the changed cell. Is that correct? And what would happen if more than one cell was changed? Would Target.Address then contain an array of addressees? And could I then loop through that array to find all the row numbers of the cells that changed? – Enantiomeer Commented 2 days ago
• 1 @BigBen & TimWilliams , thanks for the help. With your comments, the solution now appears rather obvious. Somehow I often have difficulty really understanding data models, object properties and how I can best use those. – Enantiomeer Commented 2 days ago

 |  Show 3 more comments

1 Answer 1

Sorted by: Reset to default -2

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

本文标签： excelHow can I determine which cell(s) in a watched range are changedStack Overflow