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.
- VBA window appears now.
- Press the Insert tab, then choose the Module option.
- 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
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
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
- 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
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
- 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
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
- 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
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.
