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.

  • Share your macro with us so we can see if we can help you. – CharlieRB Dec 1 '14 at 16:21

I have two solutions for you:

  • Convert your data to a table (insert - table) and you won't need to worry about formula auto fill when you add new data, so your users range will be really the range you use.

  • If the previous solution doesn't work: select a cell in a data column (not one with pre-filled formulas that extend forever) and press CTRL+ DOWN ARROW. That will jump to the last cell used in that column (only if there are no blank cells in the column).

  • On your second suggestion, doesn't that also work with formulas? – fixer1234 May 3 '15 at 5:08
  • Yes it works with formulas too, but that's not a solution for him as formulas are filled down until end of the sheet and he wants to go until end of his table only. – Máté Juhász May 3 '15 at 5:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.