vba: select table from row 3 to end for listobject

Question

Selecting a whole sheet for a listobject is done this way

ws.ListObjects.Add(xlSrcRange, Range("$A$1").CurrentRegion, , xlYes).Name = "Cost"

But now I want the same but starting with row number 3:

Dim rng As Range
Dim crng As Range
Set crng = ws.Range("$A$1").CurrentRegion
Set rng = ws.Range(ws.Range("A3"), ws.Range(crng.Columns, crng.Rows))

ws.ListObjects.Add(xlSrcRange, rng, , xlYes).Name = "Eval"

But this also selects the whole range. How can I choose the correct range?

See [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for how to find the last row. — BigBen, Dec 10 '19 at 14:36
The *hacky* way would be to use `ws.Cells(crng.Rows.Count, crng.Columns.Count)`, but highly suggest you don't do that. — BigBen, Dec 10 '19 at 14:41
Are you trying to select from Excel row 3 or the 3rd in the listobject? also if 3rd row counting the header or just the body? — Ricardo Diaz, Dec 10 '19 at 14:44
Is it not possible to get the last row and column from currentregion? — Matthias Pospiech, Dec 10 '19 at 14:44
It is possible, but not recommended. If your data has no gaps and starts in A1, then try the `ws.Cells` approach instead of `ws.Range`. — BigBen, Dec 10 '19 at 14:44
Ok. I know my rows and column count from my variables. so this works for me: `Set rng = ws.Range(ws.Range("A3"), ws.Cells(UBound(data), 10))` — Matthias Pospiech, Dec 10 '19 at 14:47

Vityata · Answer 1 · 2019-12-10T14:54:48.037

There are two cases - when a table exists in range "A3" or when there is no table there.

When a table exists

I have managed to select from A3 to the end of the table with the code below:

Sub TestMe()

    Dim ws As Worksheet
    Set ws = Worksheets(1)

    Dim myRange As Range

    With ws
        Set myRange = .Range(.Range("A3"), .Range("A3").End(xlToRight))
        Set myRange = .Range(myRange, myRange.End(xlDown))
    End With

    myRange.Select
    Stop
    'Uncomment the line below, if there is no table.
    'ws.ListObjects.Add(xlSrcRange, myRange, , xlYes).Name = "Eval40"
End Sub

However, if you uncomment the last line of the code, you will see the 1004 error, which states something like "A table cannot over lap another table", which makes sense, as the new table, named Eval40 is part of the old table which already exists.

When a table does not exist If there is no existing table at the range, then it works ok:

This solution uses `xlToRight`. in my comment I had used variables. If you add my solution to our answer I can check it. — Matthias Pospiech, Dec 12 '19 at 06:34
@MatthiasPospiech - I am afk, feel free to edit the `xlToRight` — Vityata, Dec 12 '19 at 10:53

score 0 · Answer 2 · answered Dec 10 '19 at 14:40

0

You should be able to assign the range just using row(3)

Set rng = ws.Rows(3)

answered Dec 10 '19 at 14:40

tomBob

128
6

3

OP wants all the data, not just row 3. – BigBen Dec 10 '19 at 14:40
1

this gives me a table with the complete row (up to the end), and inserts a second row (at 4) which is empty. I want the used region but only from row 3 to the end. – Matthias Pospiech Dec 10 '19 at 14:42
Ah okay, my bad I misunderstood the question. Thanks for clarifying. – tomBob Dec 10 '19 at 14:57

vba: select table from row 3 to end for listobject

2 Answers2