Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Macro to delete headers and footers

  1. #1
    Join Date
    24th May 2012
    Location
    Bradford, West Yorkshire, UK
    Posts
    7

    Macro to delete headers and footers

    I have the following code to delete Headers and Footers in Excel 2010.
    VB:
    Sub KillHeaderAndFooter() 
        Dim objHF                   As HeaderFooter 
        Dim objSection              As Section 
        For Each objSection In ActiveDocument.Sections 
            For Each objHF In objSection.Headers 
                objHF.Range.Delete 
            Next objHF 
            For Each objHF In objSection.Footers 
                objHF.Range.Delete 
            Next objHF 
        Next objSection 
    End Sub 
    
    
    This fails to compile - "User defined type not defined", with the "Dim objSection As Section" highlighted. I suspect I need a Reference, but which one? There's a lot to choose from!
    Also ('cos I know this will happen again), how can I tell which "Dim"s each Reference supports?
    Last edited by AAE; May 25th, 2012 at 07:04. Reason: revise thread title

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,302

    Re: Which "Tools/References" selection do I need

    What kind of code is that?
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  3. #3
    Join Date
    24th May 2012
    Location
    Bradford, West Yorkshire, UK
    Posts
    7

    Re: Which "Tools/References" selection do I need

    Its VBA code, with strange references to fonts, which I have now removed!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    2nd May 2008
    Location
    Ubique
    Posts
    1,891

    Re: Which "Tools/References" selection do I need

    it's VBA for Word...
    Rory
    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Programmers combine theory and practice: nothing works and they donít know why

  5. #5
    Join Date
    24th May 2012
    Location
    Bradford, West Yorkshire, UK
    Posts
    7

    Re: Which "Tools/References" selection do I need

    Hi Rory....
    Thanks!
    (On my PC it's "Microsoft Word 14.0 Object Library".)
    Once ticked, the VBA compiles OK.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    2nd May 2008
    Location
    Ubique
    Posts
    1,891

    Re: Which "Tools/References" selection do I need

    it'll compile but it won't work in Excel.
    Rory
    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Programmers combine theory and practice: nothing works and they donít know why

  7. #7
    Join Date
    24th May 2012
    Location
    Bradford, West Yorkshire, UK
    Posts
    7

    Re: Which "Tools/References" selection do I need

    Yeah - It runs to completion, but doesn't do anything!
    Any suggestions on how to use VBA to remove Headers and Footers in Excel?

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,302

    Re: Which "Tools/References" selection do I need

    Maybe
    VB:
    Option Explicit 
     
     
    Sub Remove_Header() 
        Dim cnt    As Integer 
        Dim i      As Integer 
         
        cnt = ActiveWorkbook.Worksheets.Count - 1 
         
        Application.ScreenUpdating = False 
        For i = 2 To cnt 
            Worksheets(i).Select 
            With ActiveSheet.PageSetup 
                .CenterFooter = "" 
                 'etc
            End With 
        Next i 
        Application.ScreenUpdating = True 
         
    End Sub 
    
    
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  9. #9
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Macro to delete headers and footers

    Please note the change to your thread title and in the future try to state your objective in more search friendly way.
    Besides helping others, by doing so from the start you get links to relevant posts where a similar question has been asked and answered.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  10. #10
    Join Date
    24th May 2012
    Location
    Bradford, West Yorkshire, UK
    Posts
    7

    Re: Macro to delete headers and footers

    RoyUK....
    Thanks - works like a dream (a good one!).

    AAE Super Moderator....
    Sorry if the title was misleading, but the original title was accurate in that I didn't know which Reference(s) support what I was attempting to achieve (and I still don't know what each reference library supports, unless the name makes it blindingly obvious).
    I am writing code in VBA, not using Macros, which is why I didn't include "macro" in the title.
    I am sure many threads "evolve" over time, and yes, my ultimate goal was to delete Headers and Footers using VBA, but I believed I had the code, and just needed to sort out the References bit. Shows you just how wrong I can be!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 6
    Last Post: June 21st, 2011, 03:49
  2. Replies: 3
    Last Post: February 9th, 2011, 21:12
  3. Replies: 2
    Last Post: February 1st, 2011, 15:47
  4. Replies: 2
    Last Post: August 27th, 2005, 18:19

Bookmarks

Posting Permissions

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