Excel Macro: Deleting Headers and Footers from an ActiveSheet

This Excel VBA macro removes all the headers and footers from an active sheet.

Macro Example

Sub DeleteHeadersFooters()

  With ActiveSheet.PageSetup

    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""

    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""

 End With

End Sub

Description

Starts the With structure

Sub...

  With ActiveSheet.PageSetup

The 3rd line starts the With…End With statement.

This statement tells Excel that any action apply to a Worksheet.PageSetup or Chart.PageSetup object.

The code is ActiveSheet.PageSetup but this means a Worksheet.PageSetup or Chart.PageSetup object. I’ll explain about this.

[Application.]ActiveSheet

  With ActiveSheet...

The [Application.]ActiveSheet property returns an active Worksheet or Chart object. So the Object Browser shows us that returned value is “Object”.

obj-Excel-Application-ActiveSheet

The ActiveSheet property is a member of the Excel.Global class, so we can omit the Application property.

obj-Excel-Global-ActiveSheet

ActiveSheet.PageSetup (Worksheet.PageSetup/Chart.PageSetup)

  With ActiveSheet.PageSetup

The Worksheet.PageSetup property returns a PageSetup object that contains all the page setup settings for a worksheet.

obj-Excel-Worksheet-PageSetup

And the Chart.PageSetup property returns a PageSetup object that contains all the page setup settings for a chart sheet.

obj-Excel-Worksheet-PageSetup

So the code: ActiveSheet.PageSetup means the Worksheet.PageSetup or Chart.PageSetup object.

Removing Headers

Sub...

  With ActiveSheet.PageSetup

    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""

The 5th-7th line removes the strings of headers.

The PageSetup.LeftHeader returns or sets the text on a left header.

obj-Excel-PageSetup-LeftHeader

So, the 5th line removes the left header.

    .LeftHeader = ""

Similarly, the 6th line removes the center header, the 7th line removes the right header.

    .CenterHeader = ""
    .RightHeader = ""

Removing Footers

Sub...

  With ActiveSheet.PageSetup

    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""

    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""

The 9th-11th line removes the strings of footers.

The PageSetup.LeftFooter returns or sets the text on a left footer.

obj-Excel-PageSetup-LeftFooter

So, the 9th line removes the left footer.

    .LeftFooter = ""

Similarly, the 10th line removes the center footer, the 11th line removes the right footer.

    .CenterFooter = ""
    .RightFooter = ""

Ends the With

Sub...

  With ActiveSheet.PageSetup

    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""

    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""

 End With

The 13th line ends the With…End With statement.

After the 13th line is evaluated, this macro ends.

Sub DeleteHeadersFooters()

  With ActiveSheet.PageSetup

    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""

    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""

 End With

End Sub

Properties

http://www.relief.jp/itnote/archives/excel-vba-delete-header-footer.php

Apply to

  • Excel 2013
  • Excel 2010
  • Excel 2007