dpaton05
Well-known Member
- Joined
- Aug 14, 2018
- Messages
- 2,375
- Office Version
- 365
- 2016
- Platform
- Windows
- I have some code that is meant to allow a row or a cell in that row to be selected and the row will be deleted upon a button click. If it is the first row in the table, I just need it cleared. I am sure the code used to work, but for some reason, it has stopped working.
- What it does now is it clears the first row, even if you have another row selected.
- I also don't want a run time error to appear if no cells are found.
Here is the code I have. Can someone help me please?
Code:
Sub DelSelectCostingRow()
ActiveSheet.Unprotect Password:="npssadmin"
Dim rng As Range
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("tblCosting")
On Error Resume Next
With Selection.Cells
Set rng = Intersect(.EntireRow, ActiveCell.ListObject.DataBodyRange)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "Please select a cell within a row that you want to delete.", vbCritical
Else
ActiveCell.EntireRow.Select
If ActiveSheet.ListObjects("tblCosting").ListRows(1).Range.Select Then
tbl.DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
Else
rng.Delete xlShiftUp
End If
End If
End With
Application.EnableEvents = True
End Sub