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