vba to delete a selected row in a table
Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

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

  1. #11
    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

    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

  2. #12
    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

    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
    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. #13
    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

    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.

  4. #14
    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

    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
    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. #15
    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 would rather just clear the first row instead of deleting and reinserting formulas as there are quite a few formulas. Thanks Michael.

  6. #16
    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

    Thank you for that Michael, that is perfect!!

  7. #17
    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

    Glad to help....
    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
  •