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.

Spirometer

Not sure if this will work and I do not have the ability to test it out now but you might be able to do a conditional formatting rule based on a vlookup. – Smutch

I would use vlookup, but the table is going to be sorted by BR and not name. – Spirometer

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.

Propose

I ended up having to use a hidden column to make it work, but I got what I wanted. Thank you for the help. – Spirometer

Answer 2

For the last Row

=MAX(ROW(TABLE_NAME))

Verniavernice

=INDEX(Table[Attribute],MAX(ROW(Table))-1) – Biagio

Answer 3

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.

Boatsman

Answer 4

I would suggest this code:

lastRow = ws.Range(affectedTable).Row + ws.Range(affectedTable).Rows.Count - 1

Hass

Answer 5

Expanding on a previous answer with what worked for me. Below is another solution that does not require VBA but will return the last populated row instead of the last row of the table area.

COUNT(TABLE_NAME) will return the total number of filled cells in the table. If there are blank cells, this may not work correctly.

Since we are trying to count rows, it makes more sense to use only one column of the table which will always be filled (in many cases the first column). Using the built-in COUNTA function, we can count only the cells that have a value. Use structured references to specify a single column in the table or named range:

=ROW(TABLE_NAME)-1+COUNTA(TABLE_NAME[COLUMN_NAME])

Donnettedonni

Answer 6

In 2024 many of these answers will no longer work as they create a spill range, or there are much easier solutions that use far less overhead (i.e. no volatile formulas such as offset)

Assuming one has a table or named range called Table1:

• Last row number of the table (more efficient than using max)
  • =ROW(INDEX(Table1,ROWS(Table1),1))
• Row # of last used cell in table:
  • =MAX(BYCOL(Table1,LAMBDA(eCol,MAX(FILTER(ROW(eCol),eCol<>"",0)))))
• Row # of last used cell in first col:
  • =XMATCH(FALSE,ISBLANK(INDEX(Table1,,1)),0,-1)+ROW(INDEX(Table1,1,1))-1

Paulsen

Answer 7

If you want that inside the table (totals cell for example), just use offset (no empty rows of course): [code]=OFFSET(tblName[[#Totals],[ColName]],-1,0)[/code]

If it is out of table, there are many good solutions.

Kristinakristine

Answer 8

To reference the value of the column in the last row of a table:

=INDEX(TABLE_NAME[COLUMN_NAME], ROWS(TABLE_NAME))

INDEX(TABLE_NAME[COLUMN_NAME], x) gets the x row value of a table column. ROWS(TABLE_NAME) gives the row count, hence the last row.

Underwriter