CODEWITHSUNDEEP
vbaexcel
vib500
vib500

Reputation: 91

Get row and column number of first cell in Excel table

I use a lot of tables in my code My table is somewhere in my worksheet. I know I can go to the first cell with the following code:

Worksheets("sheet").ListObjects("table").Range.Cells(1, 1).Activate

But I would like to store the row and column number in 2 integers ie. column = 3 and row = 4 if first cell of table is C4. 

Worksheets("sheet").ListObjects("table").Row and Column are not working unfortunately

Upvotes: 2

Views: 15940

Answers (2)

Chris Melville
Chris Melville

Reputation: 1518

You're nearly there. You need:

Worksheets("sheet").ListObjects("table").Range.Cells(1, 1).Row

... to return the absolute row number within the spreadsheet, of your table's first row.

Obviously, the same syntax to return the column number.

Upvotes: 3

Vityata
Vityata

Reputation: 43575

This prints the row and the column of the first cell of the table:

Public Sub TestMe()

    Dim tbl As ListObject
    Set tbl = Worksheets(1).ListObjects("Table1")
    Debug.Print tbl.Range.Cells(1, 1).Row
    Debug.Print tbl.Range.Cells(1, 1).Column

    'As a bonus:
    Debug.Print tbl.Range.Rows.Count        'total number of rows
    Debug.Print tbl.Range.Columns.Count     'total number of columns

End Sub

Very dirty way, using your code, which is activating the Cells(1,1):

Debug.Print ActiveCell.Row
Debug.Print ActiveCell.Column

Upvotes: 5

Related Questions