Excel VBA select last cell in a TABLE

The Sandbox Software/Technology
1

I have a Table in Excel. It is called “tableofdata”. In case it matters, please note that this is a table and not a range.

I want a macro that will select that table and then move the cursor to the bottom row of that data…it doesn’t matter what column of the last row as long as it’s the last row.

If I were doing this manually, I could [F5] “tableofdata” [enter] [ctrl+shift+enter] and boom! I’d be there.

I know how to do this:
Application.Goto Reference:="tableofdata"

I just don’t know how to get the cursor to the last row.

Actually, I don’t need the table to be selected/highlighted when the cursor is in the last row. I just want the cursor there.

Merci
'###

2

Never tried to move the cursor before. Why would that be better than just activating or selecting the cell or row you want?

3

I would be happy with merely activating or selecting the cell/row that I want. How do I do that when that cell/row will move from month to month? How do I tell excel/vba, “Go to the last cell or row in tableofdata”?

4

Here’s some very simple code that will only work in a very specific situation, but it might be enough to get you started.

Sub selectLastRow()
Dim table As ListObject

Set table = Sheets("Sheet1").ListObjects("tableofdata")

Sheets("Sheet1").Cells(table.Range.Rows.Count, "A").Activate

End Sub

The code assumes that your table starts in column “A”, and that there aren’t any rows above your table. Does that help?

5

Why don’t you try recording a macro while you’re doing [F5] “tableofdata” [enter] [ctrl+shift+enter] and then modify the results?

2 Likes
6

Yeah, that’s almost never the case. I’ll poke around with your code & see if I can adjust it for my needs.

7

I know there’s a way to reference the data table using databodyrange. I just don’t remember the syntax off hand

8

…I’ll research that :tup:

9

yup…it gives me

    Application.Goto Reference:="tableofdata"
    ActiveCell.Offset(53028, 1).Range("A1").Activate

That (53028,1) will be a different row,col combination for each range that I want to do this to and for any particular range it will differ month to month.

10

I wonder if I can just Range(“A1”).End(xlDown).Select. Hang on…will go try… :popcorn:

:swear: grr…nope…it just sits there in the top row of my table.

oooh, but if I try Range(“b11”).End(xlDown).Select it works. It must need help getting past the title row.
Ohhhh…I get it…the reference in Range(“xx”) is an absolute reference within the sheet and not a relative reference within the selected range or table.
Well, if these Tables never start too many rows down the sheet and are always longer than, say, 10 rows, I should be able to get by with this clumsiness.

As we used to say in Intro to Pascal, “It may be shit, but it compiles!”

11

You could also go really far down and do xlup.

12

Hey, that’s a decent idea, too. Thanks!

13

THat’s what I was going to suggest.
Start in the middle of the table, record macro of you going ctrl shift up, down, left, right.

That should give you an idea of how to get to each end of the table

14

Couldn’t you use the ResultRange property of the table to find the last row, with something like:
myTable.ResultRange.Rows.Count
and then use that to select something in the last row?

15

I think as long as you know the row the table starts on that would work.

16

I tend to use the opposite approach.

Range(cells(rows.count),“B”)).End(XlUp).select

17

I think as long as you know the row the table starts on that would work.

You could get the starting row with something like
myTable.ResultRange.Cells(1,1).Row

and add Rows.Count -1 to this to find the actual row in the worksheet.

(Actually I don’t think you need the Cells(1,1) )

18

This may come a bit late, but I had the same problem and I fixed it like this.
I thought of the row number as a coordinate, therefore an integer.
If you store the table’s rows number in a variable, datatype int, then you can dinamically go to the last row even when you add new rows. You could use the databodyrange as you were saying.

Dim lrow As Integer
lrow = task.ListRows.Count
Worksheets("Worksheet name").ListObjects("Table name").DataBodyRange(lrow, 2)

It worked for me, hope it is helpful for you

1 Like