Get Last Filled Value in Row or Column

 This can be quite a hacky thing to use, normally you'll be wanting to pick your data out of your worksheet in a slightly more intelligent way but sometimes, if you're dealing with data that hasn't been formatted particularly well you might want to grab information from the last cell in the row/column, or you may want to offet from those by a couple , depending on your needs.

Here's how I've gone about it, taking informaiton from the excellent ExcelJet initially.

The LOOKUP function in excel allows a value to be searched for in a range, similarly to the more often used VLOOKUP or HLOOKUP, in the below examples we demonstrate how to find non-blank cells and their values. Further information about how the lookup portion of this function works can be found here.

' Get the last filled value from a column
=LOOKUP(2,1/(ColRange<>""),ColRange)
=LOOKUP(2,1/(A:A<>""),A:A)

' Get the last filled value from a row
=LOOKUP(2,1/(RowRange<>""),RowRange)
=LOOKUP(2,1/(4:4<>""),4:4)


' Get the last filled row index from a column
=LOOKUP(2,1/(ColRange<>""),ROW(ColRange))
=LOOKUP(2,1/(A:A<>""),ROW(A:A))

' Get the last filled column index from a row
=LOOKUP(2,1/(RowRange<>""),COLUMN(RowRange))
=LOOKUP(2,1/(4:4<>""),COLUMN(4:4))

By taking the column or row of the cell that we're looking for we can use the INDIRECT and ADDRESS functions to pull back the cell that we're looking for. This step might seem unnecessary as we already have the above formulae to pull back the last values in a row or column but by using the approach below it's very easy to offset the cell that we're pulling the value back from.

The INDIRECT function allows a cell to be referenced by their address rather than their value and the ADDRESS function returns the address of a cell based on their row and column index respectively. This means we can swap out the above formulae for one or the other and use mathmatical operators so a -1 on the column section of the row formula will give you the cell before the last filled in the row. Here's the code:
 

' Basic Indirect Address implementation
=INDIRECT(ADDRESS(RowIndex, ColIndex)) 
=INDIRECT(ADDRESS(3, 5)) 

' Column before last filled column in row 4
=INDIRECT(ADDRESS(4,LOOKUP(2,1/(4:4<>""),COLUMN(4:4))-1)) 
' Row before last filled row in column A:A
=INDIRECT(ADDRESS(LOOKUP(2,1/(A:A<>""),ROW(A:A)), 4))

Done!