Get used Range in ListObject Table Column

Question

I am using this code to get the used range in a Listobject table column but I was wondering if there is a better way of doing this with Listobjects as this code is prone to errors.

Dim Lastrow As Integer
Dim rng as Range

Lastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Set rng = Range("B12:B" & Lastrow)

Tom · Accepted Answer · 2019-06-12T10:51:29.930

The following will select the range of your data in table column 2

Dim tbl as ListObject
Dim rng as Range

set tbl = ActiveSheet.ListObjects("YourTableName")

set rng = tbl.ListColumns(2).DataBodyRange

There is a very comprehensive guide on how to select different parts of the ListObject here

╔══════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║                                      Selecting Areas Of A Table With VBA                                     ║
╠══════════════════════════════════════╦═══════════════════════════════════════════════════════════════════════╣
║                Select                ║                               VBA Coding                              ║
╠══════════════════════════════════════╬═══════════════════════════════════════════════════════════════════════╣
║ Entire Table                         ║ ActiveSheet.ListObjects("Table1").Range.Select                        ║
╠══════════════════════════════════════╬═══════════════════════════════════════════════════════════════════════╣
║ Table Header Row                     ║ ActiveSheet.ListObjects("Table1").HeaderRowRange.Select               ║
╠══════════════════════════════════════╬═══════════════════════════════════════════════════════════════════════╣
║ Table Data                           ║ ActiveSheet.ListObjects("Table1").DataBodyRange.Select                ║
╠══════════════════════════════════════╬═══════════════════════════════════════════════════════════════════════╣
║ Third Column                         ║ ActiveSheet.ListObjects("Table1").ListColumns(3).Range.Select         ║
╠══════════════════════════════════════╬═══════════════════════════════════════════════════════════════════════╣
║ Third Column (Data Only)             ║ ActiveSheet.ListObjects("Table1").ListColumns(3).DataBodyRange.Select ║
╠══════════════════════════════════════╬═══════════════════════════════════════════════════════════════════════╣
║ Select Row 4 of Table Data           ║ ActiveSheet.ListObjects("Table1").ListRows(4).Range.Select            ║
╠══════════════════════════════════════╬═══════════════════════════════════════════════════════════════════════╣
║ Select 3rd Heading                   ║ ActiveSheet.ListObjects("Table1").HeaderRowRange(3).Select            ║
╠══════════════════════════════════════╬═══════════════════════════════════════════════════════════════════════╣
║ Select Data point in Row 3, Column 2 ║ ActiveSheet.ListObjects("Table1").DataBodyRange(3,                    ║
║                                      ║   2).Select                                                           ║
╠══════════════════════════════════════╬═══════════════════════════════════════════════════════════════════════╣
║ Subtotals                            ║ ActiveSheet.ListObjects("Table1").TotalsRowRange.Select               ║
║                                      ║                                                                       ║
╚══════════════════════════════════════╩═══════════════════════════════════════════════════════════════════════╝

Tom, I think this selects even empty cells in column 2 of the table ? — JoaMika, Jun 13 '19 at 20:12
I am trying to pass your answer to a userform combobox list but doesnt seem to work — JoaMika, Jun 13 '19 at 20:13
@JoannaMikalai Yes it will do - as would yours above. If you want non empty cells you could try using `set rng = tbl.ListColumns(2).DataBodyRange.SpecialCells(xlCellTypeConstants)` which depending on how your data is may omit the blank cells. How are you passing it to the `ComboBox.List`? It may be because you're passing the range object to it. Try using `ComboBox.List = rng.Value2` instead — Tom, Jun 17 '19 at 08:43

score 0 · Answer 2 · answered Jun 12 '19 at 10:38

Like this you will avoid any errors because you errors come from the ActiveSheet which may be different if the user interacts:

Dim Lastrow As Long 'don't use Integer is a Long cut to the integer
Dim rng As Range

With ThisWorkbook.Sheets("MySheet") 'Change MySheet to your working sheet name
    Lastrow = .Cells(.Rows.Count, 2).End(xlUp).Row
    Set rng = .Range("B12:B" & Lastrow)
End With

This way all your data will come from the sheet MySheet both the LastRow and the rng. You can notice I'm using a dot in front of Cells, Rows.count and Range. This means it's referencing the sheet on the line: With ThisWorkbook.Sheets("MySheet")

score 0 · Answer 3 · answered Mar 01 '21 at 10:19

I'using this:

Dim lastrow as Long, tbl As ListObject
With Sheets("Sheet1")
    Set tbl = .ListObjects("MyTable")
    With tbl
        'Siddharth Rout, Feb 2018
        'http://www.siddharthrout.com/index.php/2018/02/10/find-last-row-and-last-column/
        'https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba
        lastrow = .ListColumns(1).Range.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .DataBodyRange(lastrow, 1).Resize(1, 3) = Array(Date, "some text", "some text")
    End With
    Set tbl = Nothing
End With

score -1 · Answer 4 · answered Jun 12 '19 at 10:43

-1

Sub F()
    Dim tbl As ListObject
    Dim col As ListColumn
    Set tbl = ActiveSheet.ListObjects("Table1")
    Set col = tbl.ListColumns(2)
End Sub

answered Jun 12 '19 at 10:43

JohnyL

6,894
3
22
41

Get used Range in ListObject Table Column

4 Answers4