vba to delete a selected row in a table
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: vba to delete a selected row in a table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    784
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default vba to delete a selected row in a table

    • 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

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,540
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: vba to delete a selected row in a table

    Hi DAve didn't a previous version of this code work fine ??
    Version 12.7 of the quoting tool worked OK !
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,540
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: vba to delete a selected row in a table

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  4. #4
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,540
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: vba to delete a selected row in a table

    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 by Michael M; Jul 16th, 2019 at 08:22 PM.
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  5. #5
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    784
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba to delete a selected row in a table

    Quote Originally Posted by Michael M View Post
    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.

  6. #6
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    784
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba to delete a selected row in a table

    version 12.7 works but it deletes the formulas.

  7. #7
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,540
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: vba to delete a selected row in a table

    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 ??
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  8. #8
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    784
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba to delete a selected row in a table

    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

  9. #9
    Board Regular
    Join Date
    Aug 2018
    Location
    NSW, Australia
    Posts
    784
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vba to delete a selected row in a table

    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)

  10. #10
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,540
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: vba to delete a selected row in a table

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •