update workbook when row added or deleted

[Beneindias]

Hi all,

I have vba code to update the workbook when there are changes in a table.

My problem is that if I add a row to the table, it calls the code and updates the workbook, but if I delete a row, nothing hapens.

My code is this on:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim col As Range
    
    'Sets range to column "Código" in table "Obras"
    Set col = ListObjects("Obras").ListColumns("Código").Range
    
    'Checks if cell is inside range of column "Código"
    If Not Intersect(col, Target) Is Nothing Then

        'Selection IS inside the range.
        ThisWorkbook.RefreshAll

    End If
End Sub

So, this is working when I add a row, but not when I delete a row.
Is there a way to change this, or do I need to find another solution?

Thnak you all

 

[Alinah]

• Use a template
  You can use a template to run actions when a row is created, modified, or deleted.
• Use Exceljs
  When using Exceljs to edit workbooks that contain formulas, you can create two empty arrays, rowChanges and columnChanges, and add a reference (+/-) to the appropriate array whenever a row or column is added or deleted.

 

[Alex Blakenburg]

What are you actually refreshing ?
Does your workbook have Power Query tables and are there pivots stacked on top of those ?
Can you reconfirm that when you run @myall_blues's code in post #2 exactly as is (unmodified) that you don't get the message box if you delete a row.

 

[Beneindias]

I thought you said you had it fixed??

And I have...
To sum it up:

This updates the workbook when I add a row to that table:

ThisWorkbook.RefreshAll

This updates the workbook when i delete a row in the table:

 If Target.Rows.Count = Rows.Count Then
        What = "Column " & Target.Column & " Deleted along with " & Target.Columns.Count - 1 & " additonal columns"
        ThisWorkbook.RefreshAll
End If

Now, why it wasn't working with the code in my first post, I have no idea.
Why it's working now, I have no idea too...but, well, for now it's working...

I also have no idea why the last part of the code isn't working when I add or delete a row, and only works when I delete it...but I'm fairly new to work with excel's vba

 

[RoryA]

This updates the workbook when I add a row to that table:

ThisWorkbook.RefreshAll

That part runs no matter what change you make to the sheet. The bit in the If clause could not possibly run just because you delete one row. At that point Target.Rows.Count will be 1 and Rows.Count will be the number of rows on the sheet (1m+) which are not equal.

 

[Beneindias]

That part runs no matter what change you make to the sheet. The bit in the If clause could not possibly run just because you delete one row. At that point Target.Rows.Count will be 1 and Rows.Count will be the number of rows on the sheet (1m+) which are not equal.

Sorry, I pasted the code from an experimental version...
That part of the code looks like this, but it only works when I add a row:

Set col = ListObjects("Obras").ListColumns("Código").Range
   
If Not Intersect(col, Target) Is Nothing Then
      ThisWorkbook.RefreshAll
End If

But I still need to use the other bit of code to update the workbook when I delete a row:

 If Target.Rows.Count = Rows.Count Then
        What = "Column " & Target.Column & " Deleted along with " & Target.Columns.Count - 1 & " additonal columns"
        ThisWorkbook.RefreshAll
End If

The first part of the code uses the intersect because I don't want to update the workbook everytime I make a change in that sheet. Only if I change that particular column of the table.

If there are any way to refactor the code or make it more efficient, nice. If not, I don't care. It's working like that and that sheet will not be changed very often anyway

 

[RoryA]

Again, this line:

If Target.Rows.Count = Rows.Count Then

will only be true if you did something that affects every row in the worksheet. Deleting one row in a table will not make that true.

 

[Beneindias]

Again, this line:

If Target.Rows.Count = Rows.Count Then

will only be true if you did something that affects every row in the worksheet. Deleting one row in a table will not make that true.

As I said, I don't know why this is working, but it's updating my workbook when I delete a row from my table.

 

[RoryA]

The first part is triggered for me when deleting or inserting a row, not the second part.