vba to delete a selected row in a table

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,475
  • 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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,961
Office Version
2013
Platform
Windows
Hi DAve didn't a previous version of this code work fine ??
Version 12.7 of the quoting tool worked OK !
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,961
Office Version
2013
Platform
Windows
Maybe this version, if it is the Costing Tool SHeet

Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
18,961
Office Version
2013
Platform
Windows
Sorry Dave....the code in the last post won't work.
Try this one

Code:
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
 
Last edited:

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,475
Maybe this version, if it is the Costing Tool SHeet

Code:
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.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,961
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Aug 14, 2018
Messages
1,475
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
Code:
        With ThisWorkbook.Worksheets("Costing_tool")
            .Range("I5").Formula = "=IF(E5="Activities",0,[Price
ex. GST]*0.1)"

        End With
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,475
I have this formula that works in the spreadsheet so I was just trying to use vba to insert it.
Code:
=IF(E5="Activities",0,[Price 
ex. GST]*0.1)
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,961
Office Version
2013
Platform
Windows
you could use

Code:
Worksheets("Costing_tool").Range("I5").Formula = "=IF(E5=""Activities"",0,H5*0.1)"
OR simply clear the required cells

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,221
Messages
5,485,464
Members
407,502
Latest member
Bloople

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top