Reference Table Column by Name with VBA in Excel (6 Criteria)

When we have a large dataset in our Excel workbook, sometimes it is convenient to reference a column of a table just by the name instead of the whole range. Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you 6 different criteria on how to reference a column of a table by name in Excel with the VBA macro.

Download Workbook

You can download the free practice Excel workbook from here.

6 Criteria to Reference a Table Column by Name with VBA in Excel

Following this section, we will discuss 6 different criteria on how to reference a column of a table by the column header name, and how to insert, replace, display, and delete value with the reference of the column header name of a table in Excel with VBA.

Above is the example dataset that this article will follow to describe the methods.

1. Reference Table Column by Column Header Name with VBA in Excel

In this section, you will learn how to reference a table column by the column header name with VBA in Excel.

The steps to get that are given below.

Steps:

• In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• Next, in the pop-up code window, from the menu bar, click Insert -> Module.

• Then, copy the following code and paste it into the code window.

Sub ReferenceEntireColumn()
ActiveSheet.ListObjects("TblReference1").ListColumns("Student Name").Range.Select
End Sub

Your code is now ready to run.

• Now, press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

After successful code execution, look at the following image to check out the result.

As you can see from the above image, we can successfully refer to a specific column from a table with the reference of the column header with VBA.

ActiveSheet.ListObjects("TblReference1").ListColumns("Student Name").Range.Select

Read More: How to Provide Table Reference in Another Sheet in Excel

2. Embed VBA to Select Column of a Table without Header by Column Header Name

The macro that you learned in the previous section, selected the whole column of a table. But in this section, we will show you how to reference a column of a table without the header by the column header name in Excel.

Let’s see how to achieve that with VBA macro.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, copy the following code and paste it into the code window.

Sub ReferenceEntireColumnWithoutHeader()
ActiveSheet.ListObjects("TblReference2").ListColumns("Student Name").DataBodyRange.Select
End Sub

Your code is now ready to run.

• After that, Run the macro as we showed you in the above section. The result is shown in the image below.

As you can see from the above image, we can successfully refer to a specific column without the header from a table with the reference of the column header in Excel.

ActiveSheet.ListObjects("TblReference2").ListColumns("Student Name").DataBodyRange.Select

Read More: How to Sort Multiple Columns of a Table with Excel VBA (2 Methods)

3. VBA to Insert New Row in the Table with the Reference of Column Header Name

If you want to insert new rows in a table with the reference of a column header, then this section will help you figure out how to do that with VBA.

You can do that with the ListColumns property and with the Intersect method in Excel VBA.

3.1. With the ListColumns Property

Steps to insert new rows in a table with the reference of a column header with the ListColumns property are described below.

Steps:

• As shown before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, copy the following code and paste it into the code window.

Sub InsertRowByHeader()
Dim iObject As ListObject
Dim iNewRow As ListRow
Set iObject = Worksheets("Insert Row").ListObjects("TblReference3")
Set iNewRow = iObject.ListRows.Add(AlwaysInsert:=True)
x = iNewRow.Index
With iObject
    .ListColumns("Student Name").DataBodyRange(x) = "Steve"
    .ListColumns("Student ID").DataBodyRange(x) = 111
    .ListColumns("Exam Marks").DataBodyRange(x) = 88
End With
End Sub

Your code is now ready to run.

• Later, Run the macro and look at the following image to see the output.

As a result, there is a new row at the end of the table which we got by referencing the column header name with VBA.

Dim iObject As ListObject
Dim iNewRow As ListRow

Set iObject = Worksheets("Insert Row").ListObjects("TblReference3")

Set iNewRow = iObject.ListRows.Add(AlwaysInsert:=True)
x = iNewRow.Index

With iObject
    .ListColumns("Student Name").DataBodyRange(x) = "Steve"
    .ListColumns("Student ID").DataBodyRange(x) = 111
    .ListColumns("Exam Marks").DataBodyRange(x) = 88
End With

3.2. With the Intersect Method

Steps to insert new rows in a table with the reference of a column header with the Intersect method are described below.

Steps:

• At first, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, copy the following code and paste it into the code window.

Sub InsertRowByHeaderName()
Dim iObject As ListObject
Dim iNewRow As ListRow
Dim iRow As Range
Set iObject = Worksheets("Intersect").ListObjects("TblReference4")
Set iNewRow = iObject.ListRows.Add(AlwaysInsert:=True)
Set iRow = iNewRow.Range
With iObject
    Intersect(iRow, .ListColumns("Student Name").Range) = "Steve"
    Intersect(iRow, .ListColumns("Student ID").Range) = 111
    Intersect(iRow, .ListColumns("Exam Marks").Range) = 88
End With
End Sub

Your code is now ready to run.

• After that, Run the macro. Look at the image below to see the result.

Consequently, we were able to insert a new row at the end of the table which we got by referencing the column header name with VBA.

Dim iObject As ListObject
Dim iNewRow As ListRow
Dim iRow As Range

Set iObject = Worksheets("Intersect").ListObjects("TblReference4")

Set iNewRow = iObject.ListRows.Add(AlwaysInsert:=True)
Set iRow = iNewRow.Range

With iObject
    Intersect(iRow, .ListColumns("Student Name").Range) = "Steve"
    Intersect(iRow, .ListColumns("Student ID").Range) = 111
    Intersect(iRow, .ListColumns("Exam Marks").Range) = 88
End With

Read More: How to Insert or Delete Rows and Columns from Excel Table

Similar Readings

• How to Get a Count in Excel Pivot Table Calculated Field
• [Fixed!] Pivot Table Field Name Already Exists (2 Quick Methods)
• Types of Tables in Excel: A Complete Overview
• Pivot Table Custom Grouping: With 3 Criteria
• How to Extend Table in Excel (4 Ways)

4. Apply Macro to Replace Row Value by Column Header Name in Excel

You can also replace values of rows by the reference of the column header with VBA in Excel.

The steps to execute that are discussed below.

Steps:

• Firstly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Secondly, copy the following code and paste it into the code window.

Sub ReplaceRowValueByHeader()
Dim iObject As ListObject
Set iObject = Worksheets("Replace").ListObjects("TblReference5")
With iObject
    .ListColumns("Student Name").DataBodyRange(4) = "Rollins"
    .ListColumns("Student ID").DataBodyRange(4) = 112
    .ListColumns("Exam Marks").DataBodyRange(4) = 75
End With
End Sub

Your code is now ready to run.

• Next, Run the macro code.

Now consider the image above. As a result of running the macro, the values from row number 4 of the table are replaced with new values.

Dim iObject As ListObject

Set iObject = Worksheets("Replace").ListObjects("TblReference5")

With iObject
    .ListColumns("Student Name").DataBodyRange(4) = "Rollins"
    .ListColumns("Student ID").DataBodyRange(4) = 112
    .ListColumns("Exam Marks").DataBodyRange(4) = 75
End With

Read More: How to Add New Row Automatically in an Excel Table

5. Embed VBA to Display Value by the Reference of Column Header

You can also display a specific value from a table by the reference of a column header name.

Let’s see how to do that with Excel VBA.

Steps:

• At first, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, copy the following code and paste it into the code window.

Sub DisplayValueByHeader()
Dim iObject As ListObject
Dim iColumn As Range
Set iObject = Worksheets("Display").ListObjects("TblReference6")
Set iColumn = iObject.ListColumns("Student Name").DataBodyRange
MsgBox iColumn(7).Value
End Sub

Your code is now ready to run.

• Next, Run the macro and look at the following image to see the result.

Finally, we can extract the specific value that we wanted to display with the VBA code by referencing the column header name of a table in Excel.

Dim iObject As ListObject
Dim iColumn As Range

Set iObject = Worksheets("Display").ListObjects("TblReference6")

Set iColumn = iObject.ListColumns("Student Name").DataBodyRange

MsgBox iColumn(7).Value

Read More: How to Use Excel Table Reference (10 Examples)

6. VBA to Delete an Entire Row Based on Cell Value with the Reference of Column Header

You can delete an entire row based on a cell value with the reference of a column header of the table in your dataset with Excel VBA.

The steps to execute that are given below.

Steps:

• At first, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• After that, copy the following code and paste it into the code window.

Sub DeleteRowByHeader()
Dim LastRow As Long
Dim iValue As Integer
Dim iData As Integer
With ThisWorkbook.Sheets("Delete")
    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    iValue = .Range("TblReference7[Student Name]").Column
    iData = .Range("TblReference7[Student ID]").Column
    For i = LastRow To 1 Step -1
        If .Cells(i, iValue) = "Edge" And .Cells(i, iData) <> "" Then
            .Cells(i, "B").EntireRow.Delete
        End If
    Next i
End With
End Sub

Your code is now ready to run.

• Then, Run the macro and the result is shown in the image below.

As a result of the successful code execution, you can see from the above image is that we got rid of the row that used to hold the value “Edge” (previously, row number 10).

Dim LastRow As Long
Dim iValue As Integer
Dim iData As Integer

With ThisWorkbook.Sheets("Delete")

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

iValue = .Range("TblReference7[Student Name]").Column
iData = .Range("TblReference7[Student ID]").Column

    For i = LastRow To 1 Step -1
        If .Cells(i, iValue) = "Edge" And .Cells(i, iData) <> "" Then
            .Cells(i, "B").EntireRow.Delete
        End If
    Next i
End With

If you have too many rows in your dataset, then the following code is more efficient for you.

Sub DeleteRowByHeaderName()
With ThisWorkbook.Sheets("Delete").ListObjects("TblReference7")
    .Range.AutoFilter
    .Range.AutoFilter Field:=.ListColumns("Student Name").Index, Criteria1:="=Edge"
    .Range.AutoFilter Field:=.ListColumns("Student ID").Index, Criteria1:="<>"
    .Range.Offset(1).EntireRow.Delete
    .Range.AutoFilter
End With
End Sub

When you have so many rows, then we recommend executing this code instead of the above one.

This code will delete an entire row based on the specified cell value with the reference of a column header of the table with Excel VBA.

Read More: Excel VBA Code for Each Row in a Table (Add, Overwrite, Delete, etc.)

Conclusion

To conclude, this article showed you 6 effective criteria on how to reference a column of a table by name in Excel with the VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.

Related Articles

• Excel VBA to Create Table from Range (6 Examples)
• How to Make a Comparison Table in Excel (2 Methods)
• [Fix]: Formulas Not Copying Down in Excel Table (3 Solutions)
• VBA to Loop through Rows of Table in Excel (11 Methods)
• Applications of Absolute Structured References in Excel Table Formulas
• How to Use VLOOKUP Table Array Based on Cell Value in Excel