[RESOLVED] [Excel] Add (and remove) cells to a range

[Zurm]

I have a For Each loop running on the Worksheet_Change event. The loop runs through each cell in the target range, and picks out those cells that actually have changed. Once it finds a cell, I want it to add it to a global variable range.

Consider the code:

Dim lastTarget(0 To 50) As String
Dim changedCells as Range
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    Dim j As Integer
    
    For Each cell In Target        
        'Check to see if the cell actually changed
        If lastTarget(j) <> cell.Value Then             
                'Add changed cell to a list
                Set changedCells = Union(changedCells, cell)                  
        End If
        j = j + 1            
    Next cell
End Sub

Running this code gives me a "Run-time error '5': Invalid procedure call or argument" when it tries:

      'Add changed cell to a list
      Set changedCells = Union(changedCells, cell)

I'm assuming it's giving me an error because of the Union() call, but I'm passing 2 ranges as it expects. I would just pass the cell.Address to a string array, but I need the changedCells to be a range so I can use it in an Intersect() later. Anyone able to help?

***This problem has been solved, "Both parameters for Union() must NOT be Nothing."
***New issue: removing a cell from a range, see below.

[Lord Orwell]

i would guess from looking the problem is that changedcells doesn't have a value to start with. This would be an invalid argument. No value = no range.

[anhn]

Both parameters for Union() must NOT be Nothing.

If changedCells Is Nothing then
    Set changedCells = cell
Else
    Set changedCells = Union(changedCells, cell)
End If

[Zurm]

Alright, well that fixed it. Now here's one that I think will be a little more challenging.

Now I need to be able to remove cells form a range.

Here's what I am trying to accomplish so you guys can see better:
I'm attempting to create an advanced undo function. Every time a cell is changed, it generates an SQL statement listed on another sheet. What I want to be able to do, is add an "Undo change to this cell" function to the right click event when it intersects a cell in changedCells. Once the UndoChange function is run on the target cell, I need to remove that target cell from the changedCells range, and delete the SQL statement it generated for that change.

Any ideas?

[Zurm]

Well I had one idea, and that was to add the cells I want removed from changedCells to a new range. Then only act if the cell intersected changedCells, and not the other range. Works ok, not sure if there's a more efficient way to go about it though.

[Lord Orwell]

i'm not sure if you can work directly in excel from your external program, but the vba in excel has access to cell properties such as tag. You could store the undelete info in the tag.

[Zurm]

I'm working with VBA directly in excel. I have a place to store the old value of the cell, my issue is keeping track of which cells changed.

I have a list of the cell addresses, but I need to add and remove cells from a range on the fly.

[westconn1]

to remove a cell from the range, i believe you would need to loop through all the cells in the range and create a temp range, minus the deleted cell, then assign the temp range back to the original range, this may be slow, depending on the size of the range
something like

Dim r As Range, tmp As Range
deletedcelladdr = "$A$4"
Set r = Range("a1:a6")
Set r = Union(r, Range("d3"))
For Each c In r
    If Not c.Address = deletedcelladdr Then If Not tmp Is Nothing Then Set tmp = Union(tmp, c) Else Set tmp = c
Next
Set r = tmp

appears to work correctly

?r.Address
$A$1:$A$3,$A$5:$A$6,$D$3

[Zurm]

Worked perfectly.

Thanks very much everyone. You guys are great.