<

Excel macro for working with data files that have a header line - set up headings with filters and pane

Published on
10,654 Points
4,454 Views
2 Endorsements
Last Modified:
Approved
Community Pick
Terry Woods
Bridging the gap, in a way you can understand, between what you want and tech that can achieve it. Websites, web based software, email...
I regularly work with small to medium sized data files (.csv or other delimited text format) and find Excel the handiest tool for editing them. In my case, they usually have headings in the first line.

I created a handy macro to make the headings easier to read and the data easier to deal with by simply turning on the automatic data filter, freezing the headings in a separate pane, and resizing columns to match the data. Here's how to set it up yourself (Written for Excel 2003):

1. Raw text data loaded into Excel


Here's a csv file opened in Excel, unformatted: Data file, unformatted

2. The end result


Here's the end result. You can't see it here, but when you have more than a page of data, the headers stay visible at the top of the screen: The end result

3. Creating the macro


To create the macro in Excel, we can't unfortunately easily copy and paste the code in, so we'll create an empty macro first:
Choose Tools -> Macro -> Record New Macro Tools -> Macro -> Record New MacroEnter the macro name "FormatHeadings", and choose a shortcut key (I like ctrl-h), and select to store the macro in "Personal Macro Workbook" (so that you can use the macro from any file you open), then click Ok Enter macro name, shortcut key, workbookA little pop up window will appear with a square stop button. Click the square stop button to stop the macro recording.Square stop recording buttonNow we need to edit the macro to put our code in. To do this we need to first unhide the Personal Macro Workbook. Click Window -> Unhide Window -> UnhideSelect PERSONAL.XLS and click Ok. PERSONAL.XLSYou should now be in the Personal Macro Workbook "PERSONAL.XLS". Choose Tools -> Macro -> Macros. Tools -> Macro -> MacrosSelect the macro you created from the list, and click Edit Edit your MacroA "Microsoft Visual Basic" editing window should open, with your macro shown Microsoft Visual Basic windowReplace all of the text between the "Sub ..." and "End Sub" lines (leave the sub and end sub lines themselves) with the following code:
' FormatHeadings Macro
' Written 21/11/2008 by TerryAtOpus
    Rows("1:1").Select
    Selection.Font.Bold = True
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
    Range("C3").Select
    Selection.AutoFilter
    Cells.Select
    Range("AU1").Activate
    Cells.EntireColumn.AutoFit
    Range("A2").Select

Open in new window

Hit Ctrl-s or click the Save button to save your changes Click saveClose the "Microsoft Visual Basic" window.
Save the changes to your Personal Macro Workbook by clicking the Save button Save changes to PERSONAL.XLSChoose Window -> Hide to hide your Personal Macro Workbook Window -> HideAnd you're set to go! Hit ctrl-h (or the shortcut you chose) to format your headings!    
2
Comment
Author:Terry Woods
0 Comments

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Join & Write a Comment

This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month