Reputation: 3488
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
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
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
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