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

 

[myall_blues]

Are you sure it's not working?

It works for me. Try changing the line ThisWorkbook.RefreshAll to MsgBox ("Table changed") and see when you get the message.

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.
        MsgBox ("Table changed")

    End If
End Sub

 

[My Aswer Is This]

This script works for me. I used the one with Message Box
Not sure what this does:
ThisWorkbook.RefreshAll

 

[My Aswer Is This]

Do you have any additional code in the sheet you have not shown us?

 

[Beneindias]

I tried the code with the MsgBox again, and it only works when I add a row to the table.
If I delete a row, it does nothing.

And no, I don't have more code in that sheet.

Meanwhile, I made it work with this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim What As String

    Application.ScreenUpdating = False
    
    'Atualiza as tabelas quando é feita alguma alteração na folha "Tabelas Apoio"
    ThisWorkbook.RefreshAll
    
    'Atualiza as tabelas se for apagada alguma linha da tabela de obras na folha "Tabelas Apoio"
    If Target.Rows.Count = Rows.Count Then
        What = "Column " & Target.Column & " Deleted along with " & Target.Columns.Count - 1 & " additonal columns"
        ThisWorkbook.RefreshAll
    End If
    
    Application.ScreenUpdating = True
    
End Sub

It's not the most elegant solution, but it's updating the workbook.
Maybe I will polish it and add the intersect to this code, when I have a bit more free time.

Thanks

 

[RoryA]

If Target.Rows.Count = Rows.Count Then

I don't see how that could work for inserting or deleting a row (rather than a column)

 

[Beneindias]

I don't see how that could work for inserting or deleting a row (rather than a column)

Yeah, I don't know either...
Found the code online, and with a few changes, it worked

 

[RoryA]

This block will only execute if you change every row in the sheet at once:

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

and all it will do is refresh all a second time, since your code already does that once whenever any change is made (which seems very inefficient).

 

[Beneindias]

This block will only execute if you change every row in the sheet at once:

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

and all it will do is refresh all a second time, since your code already does that once whenever any change is made (which seems very inefficient).

It doesn't refresh when I delete a row of the table!!!

 

[RoryA]

I thought you said you had it fixed??