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!

 

[Alex Blakenburg]

• 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)

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

• 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

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]

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

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

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

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

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

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]

You're welcome. Glad we could help.