How to Change Column Header Name in Excel VBA (3 Examples)

Get FREE Advanced Excel Exercises with Solutions!

We can change the column header name in Excel in different ways. Excel VBA is one of them. VBA has some different built-in functions to change the header name. In this article, we will discuss some examples of Excel VBA to change the column header name.


How to Change Column Header Name in Excel VBA: 3 Examples

In this article, we will show some VBA Examples to change the header name in Excel. We have a dataset of some employees’ names, departments, and salaries. Excel VBA will change the name of the columns.


1. Change Column Header Name Based on Cell Property

In this section, we will use the VBA cells property. If you are showing your data in R1C1 style, this code is suitable for you. We will choose the cell (which is the header of a column) in the code and then change the input of that cell.

📌 Steps:

  • First, go to the Sheet Name section at the bottom of each sheet.
  • Press the right button of the mouse.
  • Choose the View Code option from the Context Menu.

Change Column Header Name Based on Cell Property

  • VBA window appears now.
  • Press the Insert tab, then choose the Module option.

Change Column Header Name Based on Cell Property

  • We enter the VBA module section.

  • Put the VBA code on the module.
Sub change_header_1()
Cells(4, 2).Value = "E_Name"
Cells(4, 3).Value = "E_ID"
Cells(4, 4).Value = "E_Salary"
End Sub

Change Column Header Name Based on Cell Property

Here, the first number indicates the row and the second number indicates the column.

  • Hit the F5 button to run the code.

We can see the result here.

  • We can also use this alternative VBA code here.
Sub change_header_2()
Cells(4, "B").Value = "E_Name"
Cells(4, "C").Value = "E_ID"
Cells(4, "D").Value = "E_Salary"
End Sub

Change Column Header Name Based on Cell Property

This will also return the same result.

Read More: How to Create Column Headers in Excel


2. Change Column Header Name Based on Range Property

In this section, we will use the Range property. If your data follows the A1 style for column heading, this VBA code will be suitable for you.

📌 Steps:

  • Go to the VBA module.
  • Copy and paste the below VBA code.
Sub change_header_3()
Range("B4").Value = "E_Name"
Range("C4").Value = "E_ID"
Range("D4").Value = "E_Salary"
End Sub

Change Column Header Name Based on Range Property

  • Now, run the code by pressing the F5 button.

Here is our desired result.

  • We can also use this VBA code, where we can declare a range without any property. Just use the square ( [ ] ) or 3rd bracket and write the cell reference.
Sub change_header_4()
[B4].Value = "E_Name"
[C4].Value = "E_ID"
[D4].Value = "E_Salary"
End Sub

Change Column Header Name Based on Range Property

Read More: How to Change Column Headings in Excel


3. Use of VBA Split Function

In this section, we will use the VBA Split function. We can also use a combination of the range and the cell property with the VBA Split function.

📌 Steps:

  • We will use the range and cells property to select the range and the Split function to split the values.
  • Now, copy and paste the following VBA code on the module.
Sub change_header_6()
Range(Cells(4, 2), Cells(4, 4)).Value = Split("E_Name E_ID E_Salary")
End Sub

VBA Split Function to change header name

  • Now, run the code by hitting the F5 button.

We can see the header name has been changed successfully.

  • We can also use this alternative VBA code here.
Sub change_header_7()
Range(Cells(4, B), Cells(4, D)).Value = Split("E_Name E_ID E_Salary")
End Sub

VBA Split Function to change header name

Read More: How to Title a Column in Excel


Change the Column Header Name from Alphabet to Numeric and Vice-Versa

In this section, we will show how to change the main column header name from the alphabet to numeric and vice-versa. Usually, an Excel file has default column marking in alphabetic. Here, we will change this to numeric.

We can see columns are in alphabet form.

📌 Steps:

  • We use the ReferenceStyle property here. It has two styles xlR1C1 and xlA1. 1st one is for numeric and 2nd one for alphabetic. Here, we will put a VBA code to transform into a numeric. So, use xlR1C1 on the code.
  • Copy and paste the following VBA code here.
Sub change_header_8()
Application.ReferenceStyle = xlR1C1
End Sub

Change the Column Header Name from Alphabet to Numeric and Vice-Versa

  • Hit the F5 button to run the code.

  • We can also change the column header from numeric to the alphabet with the following code.
Sub change_header_9()
Application.ReferenceStyle = xlA1
End Sub

Change the Column Header Name from Alphabet to Numeric and Vice-Versa

Read More: How to Change Excel Column Name from Number to Alphabet


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In this article, we showed 3 examples of Excel VBA to change column header names in Excel. We also added another example that will change Excel’s main sheet header from the alphabet to numeric and vice-versa. I hope this will satisfy your needs. If you have any questions or suggestions regarding the article, use the comment box below.


Related Articles 


<< Go Back to Rows and Columns Headings | Rows and Columns in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo