CODEWITHSUNDEEP
excelvba
jifahf
jifahf

Reputation: 11

Select Table using VBA

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

Upvotes: 1

Views: 1883

Answers (2)

norie
norie

Reputation: 9867

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

Upvotes: 0

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

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

Upvotes: 2

Related Questions