Detect row change issue

Question

Im trying to add formula to a range if rows are added or deleted.

I found the code below, in which the idea is to name a cell to RowMarker and track its changes. (for example A10000 name = RowMarker, if the row changes, the code is activated).

Determine whether user is adding or deleting rows > Link to where i took the code from.

Why is my code below not performing anything?

Private Sub Worksheet_Change(ByVal Target As Range)
    Static lngRow As Long
    Dim rng1 As Range
    Set rng1 = ThisWorkbook.Names("RowMarker").RefersToRange
    Debug.Print Rowmarker
    If lngRow = 0 Then
    lngRow = rng1.Row
        Exit Sub
    End If
    If rng1.Row = lngRow Then Exit Sub
    If rng1.Row < lngRow Then
        Application.EnableEvents = False
        Range("AB5").Formula = "=IF(SUM(AC15:AT15)>0,SUM(AC15:AT15),"""")"
        Application.EnableEvents = True
    Else
        Application.EnableEvents = False
        Range("AB5").Formula = "=IF(SUM(AC15:AT15)>0,SUM(AC15:AT15),"""")"
        Application.EnableEvents = True
    End If
    lngRow = rng1.Row
End Sub

I confess, I do not understand what you try to accomplish, even if you tried explaining... Since you do not use `Target` at all and `rng1` and `lngRow` refers to the same range, all what you say looks strange to me, sorry... — FaneDuru, May 03 '20 at 14:42
See this link, this is what im trying to accomplish: https://stackoverflow.com/questions/7479721/determine-whether-user-is-adding-or-deleting-rows — Carlsberg789, May 03 '20 at 14:44
Yes, the code should work starting with second time of event activation, when the `Static` variable receives a value. Now, what "if statement is changed to add a formula" does mean? Where do you try adding a formula? In case of inserting a row, in one of its cells? — FaneDuru, May 03 '20 at 15:10
I replace: MsgBox lngRow - rng1.Row & " rows removed" > Range("AB5").Formula = "if(AC5="";"";Sum(AC5:AE5))" — Carlsberg789, May 03 '20 at 15:34
There are two issues in your "problem"... Firstly, the formula must be written like this: `Range("AB5").Formula = "=if(AC5="""","""",Sum(AC5:AE5))"`. Even if you decimal separator is comma. And put this code **between** `Application.EnableEvents = False` and `Application.EnableEvents = True`. These lines are necessary in order to avoid triggering another event... — FaneDuru, May 03 '20 at 15:52
I think that removed the error but now nothing is happening, even though i adjusted the formula part according to your guidance, ideas? See above the updated question and the code that is not working — Carlsberg789, May 03 '20 at 16:40
What do you expect it to do? Do you have values in the range `AC15:AT15` and their sum to be `>0`? `Debug.Print Rowmarker` does not make any sense... Try changing it in `Debug.Print rng1.Address`. Does it return anything? Try putting a break on this line (select it and press F9), then go through each code line pressing F8 and see if your code does something. Is there any formula in `Range("AB5")` after running the event? — FaneDuru, May 03 '20 at 16:50
Hi, actually this helped me find the error. Copying the sheet to another workbook made me lose the RowMarker reference, therefore Debug.Print Rowmarker didnt produce anything > I added an error handler to rename the Rowmarker in these cases. Thanks for helping me locate the issue! :) Works now! — Carlsberg789, May 05 '20 at 17:17

Detect row change issue

0 Answers0