VBA - HOW TO: DYNAMIC SIZING OF DATA SET - LOOP - LASTROW
Roll through a data set using a unique row count property to dynamically identify the last row that has data.
1) LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
2) for i = 1 to lastrow, next
snippet credit goes to thespreadsheetguru