Getting Specified Column of an Excel Table as a Range-CodePudding

I have a table named table in a worksheet titled Sheet. I want to get a range consisting of the a specified column of the body of the table. The following code works:

Public Function GetColumn(colNum as Integer) As Range
    With Sheet.ListObjects("table").DataBodyRange
        Set GetColumn = Range(.Cells(1, colNum), .Cells(.Rows.Count, colNum))
    End With
End Function

I feel there must be a more elegant way to solve this problem.

It seemed like the Columns property would be the way to go but I can't seem to get the syntax right.

CodePudding user response：

You can use the Columns-property of a range to get the range for one column of that range simply by specifying the column number. Note that the index is always relative, if your range starts at C3, column(2) would refer to column D of the sheet.

So for you case:

Including the header cell:

  Set GetColumn = ws.ListObjects("Table").Range.Columns(colNum)

Without header cell:

  Set GetColumn = ws.ListObjects("Table").DataBodyRange.Columns(colNum)

Update: When you access a Range like that and iterate over it with foreach, it will only execute one iteration, containing all cells. Fix is easy:

  Set GetColumn = ws.ListObjects("Table").DataBodyRange.Columns(colNum).Cells