When writing a macro more often than not you will need to know the location of the final row in a table. As most data tables are not a fixed length you can use this code for the macro to identify the row number.
Lastrow = Range("A:F").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
This code creates a variable “Lastrow” which stores the number of the final row of your table (eg 2673), it searches columns (the range) A:F – Change this to the columns of your table.
This will then search these columns for the last cell that contains data and return the row number of the cell.
The advantage of this method using the range of A:F (or your table range) is that you can ensure that you always get the last row, even if column A has blanks in it.
You could then use the last row variable in the macro in place of the row number, for example
Range("A1:F" & Lastrow).Select
The above code would select my table A1 – F and the final row.
2673″ is replaced with –
" & Lastrow
Hope you found this a helpful tip, leave a comment if so, what Excel tricks do you rely on?