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

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.


Download Practice Workbook

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


3 VBA Examples to Change Column Header Name in Excel

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: Dealing with Tables with Changing Headers in Power Query


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 Create Excel Table with Row and Column Headers


Similar Readings


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 Promote a Row to a Column Header in Excel (2 Ways)


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: [Fixed!] My Column Headings Are Labeled with Numbers Instead of Letters


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 main sheet header from the alphabet to numeric and vice-versa. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles 

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo