I'm currently using a formula throughout a column in an Excel 2010 worksheet. It works fine, but I'm not really using the entire worksheet, I only don't want to have to worry about adding the formula every time I add a row with data. Because the excel thinks that the last significant row is the last row with a formula in it, I get a worksheet >1000000 rows long, which makes it a pain to scroll through the data already added.

Is there any work around to this problem? I have tried some kind of dynamic macro changing the scroll area of my worksheet, but I haven't gotten it to work.