CODEWITHSUNDEEP
vbarange
Matthias Pospiech
Matthias Pospiech

Reputation: 3488

vba select range for table

I want to select the range for a table. For this, I am looking at the last row and column and want to select everything from the first cell to the combination row and column.

    Set ws = ThisWorkbook.Sheets(outsheet)
    Dim rngRow As Range

    Set rngRow = ws.Range("A1").End(xlToRight)
    rngRow.Select

    Dim rngCol As Range
    Set rngCol = ws.Range("A1").End(xlDown)
    rngCol.Select

    Dim rng As Range
    Set rng = ws.Range(ws.Range("A1"), ws.Range(rngRow.Column, rngCol.row))

However, the code fails in the last line with "range failed". What is wrong, or is there a better approach?

After this, I want to call 

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

Upvotes: 2

Views: 392

Answers (3)

Miles Fett
Miles Fett

Reputation: 721

Why not use UsedRange?

Sub rangeselect()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("sheet1")

    Dim rng As Range
    Set rng = ws.UsedRange
    rng.Select

End Sub

Upvotes: 0

EvR
EvR

Reputation: 3498

Try:(as long Tabelle1 doesnot excist)

Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(outsheet)
    ws.ListObjects.Add(xlSrcRange, Range("$A$1").CurrentRegion, , xlYes).Name = "Tabelle1"

Upvotes: 1

horst
horst

Reputation: 713

Set rng = ws.Range(ws.Range("A1"), ws.Range(rngRow.Column, rngCol.row))

should be

Set rng = ws.Range(ws.Range("A1"), ws.Cells(rngCol.row, rngRow.Column))

or simply

Set rng = ws.Range(ws.Range("A1"), ws.Range(rngCol, rngRow))

Upvotes: 1

Related Questions