How to Change the Column Header Name in Excel VBA

This is an overview.

This is the sample dataset.

This code is suitable for data in R1C1 style.

Steps:

Sub change_header_1()
Cells(4, 2).Value = "E_Name"
Cells(4, 3).Value = "E_ID"
Cells(4, 4).Value = "E_Salary"
End Sub

The first number indicates the row and the second number indicates the column.

This is the output.

Sub change_header_2()
Cells(4, "B").Value = "E_Name"
Cells(4, "C").Value = "E_ID"
Cells(4, "D").Value = "E_Salary"
End Sub

It will return the same result.

This code is suitable for data in A1 style.

Steps:

Sub change_header_3()
Range("B4").Value = "E_Name"
Range("C4").Value = "E_ID"
Range("D4").Value = "E_Salary"
End Sub

This is the output.

You can also use this VBA code ( it declares a range without any property. Use the square ( [ ] ) or 3rd bracket and enter the cell reference).

Sub change_header_4()
[B4].Value = "E_Name"
[C4].Value = "E_ID"
[D4].Value = "E_Salary"
End Sub

Steps:

Sub change_header_6()
Range(Cells(4, 2), Cells(4, 4)).Value = Split("E_Name E_ID E_Salary")
End Sub

The header name changed.

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

The columns contain alphabetical characters.

Steps:

Use the ReferenceStyle property. Two styles are included: xlR1C1 and xlA1: the 1st one for numeric and the 2nd for alphabetical characters. A VBA code (containing xlR1C1) is used to transform alphabetical into numeric characters.
Enter the following VBA code:

Sub change_header_8()
Application.ReferenceStyle = xlR1C1
End Sub

You can change the column header from numeric to alphabetical with the following code.

Sub change_header_9()
Application.ReferenceStyle = xlA1
End Sub

Download this practice workbook to exercise.

Change Column Header Name.xlsm