vba to delete a selected row in a table

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,476
But wouldn't this code clear the contents of the row already without the formulas
Code:
Sub DelSelectCostingRow()
    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

    Application.EnableEvents = True

End Sub
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,963
Office Version
2013
Platform
Windows
I think we're getting a little confused...
The code I provided answers the initial question of clearing the contents of the row IF it is the only row available !!
OR deleting an entire row if not.

BUT....see post #10 to hopefully solve either issue, by either clearing the range OR clearing the entire row AND then you can reinsert the formual with the other snippet
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,476
With that code I tried to copy 3 rows from NPSS_quote_sheet and then tried to delete certain rows within costing_tool. Within costing_tool, if I select row 6 or 7 and press delete selected row, the rows delete as I want, shifting the other rows up but if I press delete row on row 5 it will only clear it and won't shift the other rows up if there are rows under it.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,963
Office Version
2013
Platform
Windows
OK...this then....

Code:
Private Sub cmdDelSelectRow_Click()
    ActiveSheet.Unprotect Password:="npssadmin"
        Dim rng As Range, lr As Long
        lr = Cells(Rows.Count, "A").End(xlUp).Row
        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 And lr = 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
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,476
I would rather just clear the first row instead of deleting and reinserting formulas as there are quite a few formulas. Thanks Michael.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,302
Messages
5,486,048
Members
407,529
Latest member
netojose

This Week's Hot Topics

Top