Refer to column name instead of "column number"

[Stevenn]

Is it possible to refer to a column in a table with something like Columns("Name").Value instead of Cells(n,m)?

 

[FormR]

Yes, that should solve the problem.

Remark: you didn't declare the variable and you gave it a meaningless name. Both are bad practice.

For ex.

...
Dim lRow As Long
 ...
lRow = objNewRow.Index
...

Understood, and thanks for the tip; lazy sunday

 

[moheganburner]

As sort of a side-bar to the original post, the solution:

Sub test()
Dim lstObj As ListObject
Dim rColumn As Range
 
' Get the table reference
Set lstObj = Worksheets("Sheet1").ListObjects("Table1")
 
' Get the column reference
Set rColumn = lstObj.ListColumns("Field1").DataBodyRange
 
' display first data value in the column
MsgBox rColumn(1).Value
 
End Sub

only works if there is data in column "Field1" of the listobject. How can the column be found if there is no data in it?

 

[moheganburner]

Ok, just to close my own loop, use:

lstObj.ListColumns("Field1").Range.Column

to find the column number of "Field1" when the table is empty.

When the table is empty, there is no DataBodyRange in any column, so that property is null.