# Delete table row after cut / paste



## Nuke_It_Newport (Dec 18, 2022)

Hi everyone-

How do I pass the name of the sheet being deactivated to another module? I've tried setting a Public variable as String, Worksheet, and Object. I can't get any of these to work correctly. I need to delete a table row on the source sheet after a row is cut and pasted to a destination sheet.


```
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    
Debug.Print "Worksheet """ & Sh.Name & """ deactivated..."

End Sub
```

Thanks!


----------



## Trebor76 (Dec 18, 2022)

Hi there,

You may have to put the sheet name in an unused cell of a worksheet and reference that but if you're using a defined table for your data you don't need to pass the sheet name to delete a row i.e. this will delete Row 3 from a table called tblMyData:


```
Option Explicit
Sub Test()

    Call DeleteTableRow("tblMyData", 3)

End Sub
Sub DeleteTableRow(strTableName As String, lngRowNum As Long)

    Dim ws As Worksheet
    Dim tbl As ListObject
    
    Application.ScreenUpdating = False
    
    For Each ws In ThisWorkbook.Sheets
        For Each tbl In ws.ListObjects
            If StrConv(tbl.Name, vbLowerCase) = StrConv(strTableName, vbLowerCase) Then
                tbl.ListRows(lngRowNum).Delete
                Application.ScreenUpdating = True
                Exit Sub
            End If
        Next tbl
    Next ws
    
    Application.ScreenUpdating = True

End Sub
```

Regards,

Robert


----------



## Peter_SSs (Dec 18, 2022)

Nuke_It_Newport said:


> after a row is cut and pasted to a destination sheet


Is that Cut/Paste done by code or manually?
If code, can we see the code


----------



## Alex Blakenburg (Dec 18, 2022)

As Robert and Peter have pointed out, there will be better ways of doing this but to answer your immediate question, you need to have your Public Declaration in a standard module under the Modules Node. I suspect you have it in ThisWorkbook.

eg


----------



## Nuke_It_Newport (Dec 18, 2022)

Thanks everyone. the cut and paste operation is done manually. I think I finally got this working correctly...

I have the following Public variable for the source worksheet:

```
Public bws As Worksheet 'Bookmark worksheet before navigation to different sheet
```

I have the following code to capture the source worksheet name:

```
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Set bws = Sh    'Set ActiveSheet to variable before SheetDeactivate event
Debug.Print "Worksheet """ & bws.Name & """ deactivated..."
End Sub
```

I have the following code that deletes any blank rows on the destination sheet, resizes the table, then performs the same actions on the source sheet. This now works correctly.

```
Option Explicit

Sub DynResizeTable() 

On Error Resume Next

Dim lRow As Long, tbl As ListObject, EntireRow As Range, ws As Worksheet
'Resize Destination or ActiveSheet table
Set ws = ActiveSheet
Set tbl = ws.ListObjects(1)

Debug.Print ("Resizing table on ActiveSheet """ & ws.Name & """")
With ws
    lRow = .Range("A" & Rows.Count).End(xlUp).Row
    'Delete blank rows until last used row
     For I = lRow To 2 Step -1   'Loop through ws rows, starting with last used row
       Set EntireRow = .Cells(I, 1).EntireRow
       If Application.WorksheetFunction.CountA(EntireRow) = 0 Then
         EntireRow.Delete
       End If
     Next I
    lRow = .Range("A" & Rows.Count).End(xlUp).Row
    tbl.Resize .Range("A3:S" & lRow)
    .PivotTables(1).PivotCache.Refresh
End With

'*************************************************************************************************************************
'Resize source table
Set tbl = bws.ListObjects(1)

Debug.Print ("Resizing table on source sheet """ & bws.Name & """")
With bws
    lRow = .Range("A" & Rows.Count).End(xlUp).Row
    'Delete blank rows until last used row
     For I = lRow To 2 Step -1   'Loop through ws rows, starting with last used row
       Set EntireRow = .Cells(I, 1).EntireRow
       If Application.WorksheetFunction.CountA(EntireRow) = 0 Then
         EntireRow.Delete
       End If
     Next I
    lRow = .Range("A" & Rows.Count).End(xlUp).Row
    tbl.Resize .Range("A3:S" & lRow)
    .PivotTables(1).PivotCache.Refresh
End With

End Sub
```

The end goal of this is for a user to cut or copy a row or rows, paste these rows anyhwere on the destination sheet, and the code will "clean everything up", deleting blank rows, and placing all the rows in the table on the worksheet. I've struggled to get this to work correctly for days! If anybody has any suggestions how to code this more efficiently, or sees any potential problems, please let me know. I'm learning every day, thanks to you all! Hopefully some will find this code useful.
Thank you! 👍


----------



## Nuke_It_Newport (Dec 19, 2022)

Alex Blakenburg said:


> As Robert and Peter have pointed out, there will be better ways of doing this but to answer your immediate question, you need to have your Public Declaration in a standard module under the Modules Node. I suspect you have it in ThisWorkbook.
> 
> eg
> View attachment 81160
> ...





Alex Blakenburg said:


> As Robert and Peter have pointed out, there will be better ways of doing this but to answer your immediate question, you need to have your Public Declaration in a standard module under the Modules Node. I suspect you have it in ThisWorkbook.
> 
> eg
> View attachment 81160
> ...


Thanks. I do have the Public Declaration in a module under the Modules node, but I wasn't aware this made a difference (vs. being located in ThisWorkbook). I learn something new every day. You guys rock!


----------



## Alex Blakenburg (Dec 20, 2022)

Nuke_It_Newport said:


> or sees any potential problems,


Deleting the EntireRow is fine as long as you don't have any data to either side of the table.
If you do then something like this should work:


```
Set EntireRow = Intersect(.Cells(I, 1).EntireRow, tbl.Range)
```


----------



## Nuke_It_Newport (Dec 20, 2022)

Alex Blakenburg said:


> Deleting the EntireRow is fine as long as you don't have any data to either side of the table.
> If you do then something like this should work:
> 
> 
> ...


Thanks! I hadn't thought of that. What would the syntax be to Set "EntireRow" for use in the following?


In the original code I posted above, I'm resizing the table using a static column of "S". I'd like to replace Range("A3:S" & lRow) with a Range starting at A3, ending at the last table row and column.


```
tbl.Resize .Range("A3:S" & lRow)
```


In Workbook_SheetSelectionChange, I'm using the following to highlight the active row. I would like to limit this to the table columns.


```
With Target.EntireRow.FormatConditions.Add(Type:=xlExpression, Formula1:="=TRUE")
                .SetFirstPriority
                .Interior.Color = 10092543
            End With
```

Let me know if I should start a new thread for these questions.
Thank you!


----------



## Peter_SSs (Dec 20, 2022)

Nuke_It_Newport said:


> Dim lRow As Long, tbl As ListObject, *EntireRow* As Range, ws As Worksheet


As an aside: As a general rule it is dangerous to use words that already have special meaning in vba as Variable/Procedure/Module names.


----------



## Nuke_It_Newport (Dec 20, 2022)

Peter_SSs said:


> As an aside: As a general rule it is dangerous to use words that already have special meaning in vba as Variable/Procedure/Module names.


Oof! Thank you. I got the original code snippet from somewhere on the internet, and didn't realize that was an "assigned" name. I'll change that immediately. That's what I get for copying code from somewhere other than Mr.Excel.com. 😑😄


----------



## Nuke_It_Newport (Dec 18, 2022)

Hi everyone-

How do I pass the name of the sheet being deactivated to another module? I've tried setting a Public variable as String, Worksheet, and Object. I can't get any of these to work correctly. I need to delete a table row on the source sheet after a row is cut and pasted to a destination sheet.


```
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    
Debug.Print "Worksheet """ & Sh.Name & """ deactivated..."

End Sub
```

Thanks!


----------



## Alex Blakenburg (Dec 21, 2022)

Nuke_It_Newport said:


> In the original code I posted above, I'm resizing the table using a static column of "S". I'd like to replace Range("A3:S" & lRow) with a Range starting at A3, ending at the last table row and column.
> 
> 
> ```
> ...


For this one, the following should work:


```
Dim lCol As Long
    With ws
        lRow = .Range("A" & Rows.Count).End(xlUp).Row
        lCol = tbl.Range.Cells(1, 1).End(xlToRight).Column
        tbl.Resize .Range(tbl.Range.Cells(1, 1), .Cells(lRow, lCol))
    End With
```




Nuke_It_Newport said:


> In Workbook_SheetSelectionChange, I'm using the following to highlight the active row. I would like to limit this to the table columns.
> 
> 
> ```
> ...



You might need to do a new thread for this one.
You would need to show all or more the code. eg does your code ensure that the Target cell is in the table bounds. Have you set a table object that can be used ?
eg if you had set tbl as in this code you could use the same intersect code.

```
With Intersect(Target.EntireRow, tbl.Range).FormatConditions.Add(Type:=xlExpression, Formula1:="=TRUE")
        .SetFirstPriority
        .Interior.Color = 10092543
    End With
```


----------



## Nuke_It_Newport (Dec 23, 2022)

Alex Blakenburg said:


> For this one, the following should work:
> 
> 
> ```
> ...


Thank you so much! You've been most helpful. I have set a table object, 


Alex Blakenburg said:


> For this one, the following should work:
> 
> 
> ```
> ...


Thank you! The first code snippet works perfect. For the second code snippet, I do have a table object. I quickly learned what you mean about the need to check if the target is within the table range. I think I can figure that one out with the info you've given me. If not, I'll start a new thread. Thanks for your help!


----------



## Alex Blakenburg (Dec 23, 2022)

You're welcome. Glad we could help.


----------

