This works on sheets with both hidden rows and autofilters. It will also NOT give you the incorrect row if a cell below the last cell with a value has been formatted (which will cause the usedrange to be greater than the row you are looking for).

http://stackoverflow.com/questions/14200392/finding-the-last-row-of-an-excel-spreadsheet-when-the-last-row-is-hidden