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.

Dataset to Reference Table Column by Name with VBA in Excel

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.

Reference Table Column by Header Name with VBA in Excel

  • 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.

VBA Code Explanation

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

This line of code first selects the table (“TblReference1” is the table name in our dataset), then selects the specified column by the reference of the column header name (“Student Name” is the header name of the column).

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.

Reference Table Column by Header Name without Header with VBA in Excel

  • 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.

VBA Code Explanation

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

This line of code first selects the table (“TblReference2” is the table name in our dataset), then selects the specified column by the reference of the column header name (“Student Name” is the header name of the column). While selecting the whole column, it skips the header because we implemented the DataBodyRange property here. DataBodyRange property returns the result containing the range from the list between the header row and the insert row.

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.

Reference Table Column by Name to Insert Row with ListColumns with VBA in Excel

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

Result of Reference Table Column by Name to Insert Row with ListColumns with VBA in Excel

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.

VBA Code Explanation

Dim iObject As ListObject
Dim iNewRow As ListRow

Defining the variables.

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

Storing the sheet name (“Insert Row” is the sheet name in our dataset) and the table name (“TblReference3” is the table name) in the defined variable.

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

Defining a new variable to store new values there later in the code.

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

This piece of code:

  • Stores “Steve” in the “Student Name” column.
  • Store “111” in the “Student ID” column.
  • Stores “88” in the “Exam Marks” column.

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.

Reference Table Column by Name to Insert Row with Intersect with VBA in Excel

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

Result of Reference Table Column by Name to Insert Row with Intersect with VBA in Excel

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.

VBA Code Explanation

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

Defining the variables.

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

Storing the sheet name (“Intersect” is the sheet name in our dataset) and the table name (“TblReference4” is the table name) in the defined variable.

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

Defining a new variable to store new values there later in the code.

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

This piece of code:

  • Stores “Steve” in the “Student Name” column.
  • Store “111” in the “Student ID” column.
  • Stores “88” in the “Exam Marks” column.

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


Similar Readings


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.

Reference Table Column by Name to Replace Value with VBA in Excel

  • 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.

VBA Code Explanation

Dim iObject As ListObject

Defining the variable.

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

Storing the sheet name (“Replace” is the sheet name in our dataset) and the table name (“TblReference5” is the table name) in the defined variables.

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

This piece of code replaces values in row number 4 with the new values provided:

  • Stores “Rollins” in the “Student Name” column.
  • Store “112” in the “Student ID” column.
  • Stores “75” in the “Exam Marks” column.

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.

Reference Table Column by Name to Display Value with VBA in Excel

  • 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.

VBA Code Explanation

Dim iObject As ListObject
Dim iColumn As Range

Defining the variables.

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

Storing the sheet name (“Display” is the sheet name in our dataset) and the table name (“TblReference6” is the table name) in the defined variable.

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

Storing the column header name (“Student Name” is the column header name of the table in our dataset) in the defined variable.

MsgBox iColumn(7).Value

Returns the 7th value from the specified column in the MsgBox (“Brock” is the 7th value of the column in our table).

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.

Reference Table Column by Name to Delete Value with VBA in Excel

  • 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 valueEdge (previously, row number 10).

VBA Code Explanation

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

Defining the variables.

With ThisWorkbook.Sheets("Delete")

Selecting the worksheet (“Delete” is the sheet name in our workbook)

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

Storing the last row count in the defined variable.

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

Storing the table name (“TblReference7” is the table name) and the column header names (“Student Name” and “Student ID”) in the defined variables.

    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

This piece of code starts looping from the last row of the range and moves upward. Once it finds the value that we provided in the code (the value is “Edge” in our case), it deletes the entire row from column B. After that, it leaves the loop and starts the iteration again. It continues iterating the whole column until it fulfils the condition.

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.

Reference Table Column by Name to Delete Value for Many Rows with VBA in Excel

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

Tags:

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo