Attend Excelapalooza
Thanks Thanks:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Macro to delete all data except formulas and table headings

  1. #1
    Board Regular
    Join Date
    Jun 2005
    Posts
    186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to delete all data except formulas and table headings

    I created a clear all button on a sheet that needs to be cleared at the end of the year, ready for use at the start of the next year. The macro worked, but it also cleared out the table headings. (the names of each column)

    This was the macro I used:

    Code:
    Sub ClearAllButFormulas()
        Dim wks As Worksheet
    
        For Each wks In Worksheets
            'ignore errors in case there is only formulas
            On Error Resume Next
            wks.Cells.SpecialCells _
              (xlCellTypeConstants, 23).ClearContents
            On Error GoTo 0
        Next
        Set wks = Nothing
    End Sub
    What do I need to add to it to prevent it from deleting the table headings?

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,163
    Post Thanks / Like
    Mentioned
    213 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Macro to delete all data except formulas and table headings

    How about
    Code:
    Sub ClearAllButFormulas()
        Dim wks As Worksheet
    
        For Each wks In Worksheets
            'ignore errors in case there is only formulas
            On Error Resume Next
            wks.UsedRange.Offset(1).SpecialCells _
              (xlCellTypeConstants, 23).ClearContents
            On Error GoTo 0
        Next
        Set wks = Nothing
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  3. #3
    Board Regular
    Join Date
    Jun 2005
    Posts
    186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to delete all data except formulas and table headings

    I tried that. It did the same thing: cleared data and table headings, changing them to column 1, column 2, column 3, etc. rather than the names I had entered in.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,163
    Post Thanks / Like
    Mentioned
    213 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Macro to delete all data except formulas and table headings

    What row are your heading on?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,163
    Post Thanks / Like
    Mentioned
    213 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Macro to delete all data except formulas and table headings

    Change of plan, If you have 1 table per sheet try
    Code:
    Sub ClearAllButFormulas()
        Dim wks As Worksheet
        Dim Tbl As ListObject
    
        For Each wks In Worksheets
            'ignore errors in case there is only formulas
            On Error Resume Next
            Set Tbl = wks.ListObjects(1)
            Tbl.DataBodyRange.SpecialCells _
              (xlCellTypeConstants, 23).ClearContents
            On Error GoTo 0
        Next
        Set wks = Nothing
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  6. #6
    Board Regular
    Join Date
    Jun 2005
    Posts
    186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to delete all data except formulas and table headings

    I didn't try that macro because I have two tables on each sheet.

    Headings are on row 3 and row 27.

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,488
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Macro to delete all data except formulas and table headings

    Quote Originally Posted by dsrt16 View Post
    I didn't try that macro because I have two tables on each sheet.

    Headings are on row 3 and row 27.
    Are they real Excel Table objects or just normal ranges of cells on a sheet that you think of as being a table? If just normal ranges, what are the column extents of each table?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,163
    Post Thanks / Like
    Mentioned
    213 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Macro to delete all data except formulas and table headings

    Ok try this
    Code:
    Sub ClearAllButFormulas()
        Dim wks As Worksheet
        Dim Tbl As ListObject
    
        For Each wks In Worksheets
            'ignore errors in case there is only formulas
            On Error Resume Next
            For Each Tbl In wks.ListObjects
               Tbl.DataBodyRange.SpecialCells _
                 (xlCellTypeConstants, 23).ClearContents
               On Error GoTo 0
            Next Tbl
        Next
        Set wks = Nothing
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  9. #9
    Board Regular
    Join Date
    Jun 2005
    Posts
    186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to delete all data except formulas and table headings

    That worked perfectly. Thanks!

  10. #10
    Board Regular
    Join Date
    Jun 2005
    Posts
    186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to delete all data except formulas and table headings

    Quote Originally Posted by Rick Rothstein View Post
    Are they real Excel Table objects or just normal ranges of cells on a sheet that you think of as being a table? If just normal ranges, what are the column extents of each table?
    Yes, they are real table objects. Fluff's code worked.

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
  •