vba to delete a selected row in a table

[dpaton05]

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

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

 

[Michael M]

Hi DAve didn't a previous version of this code work fine ??
Version 12.7 of the quoting tool worked OK !

 

[Michael M]

Maybe this version, if it is the Costing Tool SHeet

Private Sub cmdDelSelectRow_Click()
    ActiveSheet.Unprotect Password:="npssadmin"
        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 cell within a row that you want to delete.", vbCritical
            Else
                If ActiveCell.Row = 5 Then ActiveCell.Row.ClearContents
                rng.Delete xlShiftUp
            End If
        End With
    Application.EnableEvents = True
    ActiveSheet.Protect Password:="npssadmin"
End Sub

 

[Michael M]

Sorry Dave....the code in the last post won't work.
Try this one

Private Sub cmdDelSelectRow_Click()
    ActiveSheet.Unprotect Password:="npssadmin"
        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 cell within a row that you want to delete.", vbCritical
            Else
                If ActiveCell.Row = 5 Then
                Rows(5).ClearContents
                Else
                rng.Delete xlShiftUp
                End If
            End If
        End With
    'ListObjects("NPSS_quote").ListColumns("10%Increase").DataBodyRange.Value = "1"
    Application.EnableEvents = True
    'ActiveSheet.Protect Password:="npssadmin"
End Sub

 

[dpaton05]

Maybe this version, if it is the Costing Tool SHeet

Private Sub cmdDelSelectRow_Click()
    ActiveSheet.Unprotect Password:="npssadmin"
        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 cell within a row that you want to delete.", vbCritical
            Else
                If ActiveCell.Row = 5 Then ActiveCell.Row.ClearContents
                rng.Delete xlShiftUp
            End If
        End With
    Application.EnableEvents = True
    ActiveSheet.Protect Password:="npssadmin"
End Sub

I get an error with this code. It highlights the second .row in this line
If ActiveCell.Row = 5 Then ActiveCell.Row.ClearContents
and says invalid qualifier.

 

[dpaton05]

version 12.7 works but it deletes the formulas.

 

[Michael M]

See post #4 Dave.
So if it is the only row in the table, you don't want to clear the row ??
You only want to clear a range of cells...is that correct.

AND
I thought you had code to insert the formulas when rows were copied to the costing tool Sheet ??

 

[dpaton05]

I tried using the code I already have that inserts a formula but it has a syntax error. The code was on the sheet NPSS_quote_sheet but I am trying to get it to work on Costing tool and I get a syntax error with trying to insert this formula

        With ThisWorkbook.Worksheets("Costing_tool")
            .Range("I5").Formula = "=IF(E5="Activities",0,[Price
ex. GST]*0.1)"

        End With

 

[dpaton05]

I have this formula that works in the spreadsheet so I was just trying to use vba to insert it.

=IF(E5="Activities",0,[Price 
ex. GST]*0.1)

 

[Michael M]

you could use

Worksheets("Costing_tool").Range("I5").Formula = "=IF(E5=""Activities"",0,H5*0.1)"

OR simply clear the required cells

Private Sub cmdDelSelectRow_Click()
    ActiveSheet.Unprotect Password:="npssadmin"
        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 cell within a row that you want to delete.", vbCritical
            Else
                If ActiveCell.Row = 5 Then
                Range("A5:H5").ClearContents
                Else
                rng.Delete xlShiftUp
                End If
            End If
        End With
    'ListObjects("NPSS_quote").ListColumns("10%Increase").DataBodyRange.Value = "1"
    Application.EnableEvents = True
    'ActiveSheet.Protect Password:="npssadmin"
End Sub