Excel VBA code to add column header automatically

add-column-header-automatically

What does this code do?

Firstly, this code is for adding column heading automatically in excel which we use in our day to day jobs. By doing this automation, we save quite amount of time on adding the same headings daily. There are two ways of automating column heading, first by recording actual steps as a macro ( Check this article on how to record a macro ). Recording macros are much faster and accurate but customization in later is a little bit messy. secondly by creating VBA codes as per your requirements knowledge in programming in VBA is required.

Adding headings alone is not solely good to see, so we do add some styling properties like column width, font color, font style, and cell background, etc to make it look better and eye catchy.

In this tutorial, I have covered using VBA code to automate column heading, let us see the steps.

Step 1:

First, you should know what are all the heading you will be using and the order of these headings. In this example, I have taken five headings SalespersonID, Product, Quantity, Price/Unit, and Total sales.

Step 2:

Assign each heading to cell address starting from column A and so on. As per our example, the cell A1 will have a value SalespersonID and the Cell B1 will have Product, you can add as many column headings as per your wish.

Sub addheader()
Range("A1").Value = "SalespersonID"
Range("B1").Value = "Product"
Range("C1").Value = "Quantity"
Range("D1").Value = "Price/Unit"
Range("E1").Value = "Total sales"
End Sub
Result of the above code:
add-column-header-automatically
Step 3: (Styling cells)

You have successfully automated the headings, but in order to make it look good, you should style these heading. Let us see how

Sub addheader()
Range("A1").Value = "SalespersonID"
Range("B1").Value = "Product"
Range("C1").Value = "Quantity"
Range("D1").Value = "Price/Unit"
Range("E1").Value = "Total sales"

'Styling cells
Worksheets("Sheet1").Columns("A:E").ColumnWidth = 15'column width
Range("A1:E1").HorizontalAlignment = xlCenter'Text Alignment
Range("A1:E1").Font.Size = 12'Font Size
Range("A1:E1").Font.FontStyle = "Bold"'Font Style
Range("A1:E1").Interior.ColorIndex = 29'Cell Background color
Range("A1:E1").Font.Color = vbWhite'Text color
End Sub
Final Output:
add-column-header-automatically

As you can see we achieved what we want, the code did it right. Also, you can work around styling it as per your taste. Alternately, if you want you can add a button and assign this macro to that button, making it simple to access. The project source code file is available for download you can download it from the below link. Try experimenting with this code to get clarity.

Hope you liked this article, if so please share it with your friends.

Excel Logo

Download project file

If you want more clarity or demo of this code, please watch the below youtube video.

Video: Excel VBA code to add column header automatically

Share on:
Most Popular