Delete table row after cut / paste

[Nuke_It_Newport]

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]

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]

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]

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]

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]

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

View attachment 81161

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

View attachment 81161

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]

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]

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)

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]

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]

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.