How do I reference the last row in a named Excel table?

Question

I am currently trying to format one column of my table so that if there are any names that match in another column, the cell in the original column will be highlighted. Here's an example of what I mean:

Row 10 has a prerequisite of the M6A1. However, row 11 has a name of M6A1. I would like the M6A1 in row 10 to be highlighted.

To do this, I figured I would use COUNTIF, with the range from the current row (10 in this case) down to the bottom row (14). I don't want to hard-code in 14 however, as the list length will change. Therefore, I thought that I could just call the last row in the table, but that's not a feature apparently. I would like to know either how to dynamically reference the last row in this table, or if there is a better way to do this.

Answer 1

If you consider using built-in Excel functions you can get the last row of the table or named range this way:

• =ROW(NAMED_RANGE or TABLE_NAME)-1+ROWS(NAMED_RANGE or TABLE_NAME) - for the last row of the table;
• =ROW(NAMED_RANGE or TABLE_NAME)-1+COUNT(NAMED_RANGE or TABLE_NAME) - for the last record in the table.

Answer 2

To get the last row of your table, use Range("A1").End(xlDown).Row, provided your "A" column does not have any blank cells. If it does, a better way to do it is to use Range("A1000000").End(xlUp).Row. The .End(Direction) method goes to the last cell before a blank a cell in a given direction (Up, Down, Left or Right, preceded by xl), starting from the specified Range, and the .Row method gets the row number of a given Range/Cell.