Results 1 to 8 of 8

Thread: Modify code so that formulas in table rows are not deleted
Thanks Thanks: 0 Likes Likes: 0

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

    Default Modify code so that formulas in table rows are not deleted

    I have the following code:
    Code:
    Sub DeleteCostingLine()
        Dim ws As Worksheet
        Dim tbl As ListObject
            Set ws = ActiveSheet
            Set tbl = ws.ListObjects("tblCosting")
    
        tbl.ListRows(tbl.ListRows.Count).Delete
        Worksheets("Costing_tool").Range("AB5").Value = "1"
    End Sub
    This deletes everything in the selected row in a table but I don't want it to delete formulas, can someone help me with changing this code please?

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

    Default Re: Modify code so that formulas in table rows are not deleted

    Actually, I just tried it again and it seemed to work.

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

    Default Re: Modify code so that formulas in table rows are not deleted

    Actually, I put up the wrong code, this is the code. Could someone change this so formulas are not deleted please?

    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
                    rng.Delete xlShiftUp
                End If
            End With
        'ListObjects("NPSS_quote").ListColumns("10%Increase").DataBodyRange.Value = "1"
        Application.EnableEvents = True
        'ActiveSheet.Protect Password:="npssadmin"
    End Sub

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

    Default Re: Modify code so that formulas in table rows are not deleted

    I tried to change it myself but it didn't work. I just thought that I could check if it was the first row in the table and if it was, I would just clear the contents but if it was a later row, the row would just be deleted.

    This is my attempt, can someone let me know where I went wrong please as I am not very sure of the syntax?

    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(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 .Row(1) = True Then
                            tbl.DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).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

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

    Default Re: Modify code so that formulas in table rows are not deleted

    So you don't want to delete the row, you just want to clear all contents, EXCEPT formulas ??
    Which columns in the row contain formulas ??
    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]

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

    Default Re: Modify code so that formulas in table rows are not deleted

    The formula deletes everything in the row and that is ok as it is a table but when it gets to the only row left in the table, it deletes the formulas in the cells and as there is no row above it with the formulas in it, when I copy from quoting tool, it doesn't copy in and have the formulas auto populate.

    Maybe I could have code in cmdSend that puts the formula in each column that has one for the rows that are copied?

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

    Default Re: Modify code so that formulas in table rows are not deleted

    I worked it out with the following code:

    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(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
                        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
        'ListObjects("NPSS_quote").ListColumns("10%Increase").DataBodyRange.Value = "1"
        Application.EnableEvents = True
        'ActiveSheet.Protect Password:="npssadmin"
    End Sub

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

    Default Re: Modify code so that formulas in table rows are not deleted

    Hmmm, seems to me you're getting the hang of this VBA thing...

    I'm not sure you need the .Select line though
    Code:
    ActiveCell.EntireRow.Select
    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
  •