Delete a single row in a table except the last.

[tamaramaau]

Hi Guys,

This is my first post. I am experienced with Excel but an amateur with VBA. I have code which deletes a table row (containing the active cell) that works perfectly except that it also deletes the last row in the table and the table disappears. I would like to insert some additional code to prevent a row from being deleted if it is the last row. Thanks for your help in advance. Here's the code I am using -

Application.ScreenUpdating = False

Dim rng As Range

On Error Resume Next
With Selection.Cells(1)
Set rng = Intersect(.EntireRow, ActiveCell.ListObject.DataBodyRange)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "Please select a valid table cell.", vbCritical
Else
rng.Delete xlShiftUp
End If
End With

Application.ScreenUpdating = True

End Sub

[Andy Pope]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

this works for me

Please Login or Register  to view this content.