Select Table using VBA

Question

I want to select the whole table using VBA. Without using VBA, I could use Ctrl+A function and select the whole table. I tried a different method but still to no avail.

1st method:

Dim last_row As Long
Dim last_column As Long

Sheets("sheet1").Select
last_row = Cells(Rows.Count, 1).End(xlUp).Row


last_column = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

Range("A1:last_row & last_column").Select
'ActiveCell.Offset(last_row, last_column).Range("A1").Select

2nd method:

Sheets("Sheet1").Select

Dim CSLastRow As Long
Dim CSLastColIndex As Long
Dim Last As Long

'finding last row
CSLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
'finding last col
CSLastColIndex = Cells.Find(What:="I will recommend this course to others.", _
                after:=Range("A1"), _
                LookAt:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Column
ColumnLetter = Split(Cells(1, CSLastColIndex).Address, "$")(1)

Last = concatenate(ColumnLetter, CSLastRow)

Range("A1:" & Last).Select

if you have contiguous data (no empty rows and no empty columns) starting in cell `A1`, then it's a no-brainer:`Range("A1").CurrentRegion.Select`. — VBasic2008, Mar 23 '21 at 09:28
Why do you want to **Select** the table at all? It is rarely necessary, and can lead to problems. Please see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Much better to determine the bounds, and set it to a range (or listobject) variable. — Ron Rosenfeld, Mar 23 '21 at 11:55

score 2 · Answer 1 · answered Mar 23 '21 at 04:27

There are syntax errors in both of your codes so they will produce an error. I am not suggesting you any new code, but fixed your original codes and see if they work for you.

Sub Macro1()
Dim last_row As Long
Dim last_column As Long

Sheets("sheet1").Select
last_row = Cells(Rows.Count, 1).End(xlUp).Row


last_column = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

Range("A1", Cells(last_row, last_column)).Select

End Sub


Sub Macro2()
Sheets("Sheet1").Select

Dim CSLastRow As Long
Dim CSLastColIndex As Long
Dim Last As String
Dim ColumnLetter As String

'finding last row
CSLastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

'finding last col
CSLastColIndex = Cells.Find(What:="I will recommend this course to others.", _
                after:=Range("A1"), _
                LookAt:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Column
ColumnLetter = Split(Cells(1, CSLastColIndex).Address, "$")(1)

Last = ColumnLetter & CSLastRow

Range("A1:" & Last).Select
End Sub

norie · Answer 2 · 2021-03-23T12:02:32.500

0

If it's an actual table you can use the ListObjects collection.

Dim tbl As ListObject
Dim rng As Range

    ' set reference to first table on active sheet
    Set tbl = ActiveSheet.ListObjects(1l

    ' set reference to the table's range
    Set rng = tbl.Range
    
    ' select it if you must
    tbl.Range.Select

edited Mar 23 '21 at 12:02

answered Mar 23 '21 at 03:49

norie

9,609
2
11
18

Select Table using VBA

2 Answers2