problems with the default area of and excel worksheet

0 pts.
Tags:
Microsoft Office
Hello I am have a problem with spreadsheets in general, regarding the default area of a worksheet. If I have 300 rows of data in a worksheet (with columns A to M filled), and press CTRL & END, the cell selected is the bottom rightmost cell on row 300 ie M300. If I now delete 150 rows in the middle & press CTRL & END it still selects M300, even though the last data is now M150. This might seem trivial, but when using pivot tables to analyse data the rows may vary between 20 & 30,000 and scrolling takes for ever. Hope some one out there can help! Cheers John Woolliscroft
ASKED: March 2, 2006  7:26 AM
UPDATED: March 6, 2006  8:50 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

You could try setting the print area to exclude the empty rows. I think that may work.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Woollysox
    I am sorry to say that option doesn't work either. I was wondering if a defalt sheet area could be setup using Insert/Define/Name to select the area - but I cannot see anything as a default.
    0 pointsBadges:
    report
  • Andymadge
    The reason for this is that Excel still classes the cells down to M300 as being part of the spreadsheet because it is still storing formatting info for those cells. Once you close the file and open it again you should find that CTRL & END takes you to the correct place. If not, it definitely will if you save the file with a different name. Another option is to use CTRL & DOWN ARROW, then CTRL & RIGHT ARROW. This will work in your situation without saving the file. (Obviously if you're making a selection, you'll also need to hold down the SHIFT key) One more thing you may find useful - look into using Lists (Data/List/Create List...) and basing your pivot tables on the list - it will grow/shrink with the data
    0 pointsBadges:
    report
  • Boecherer
    Copy the remaining 150 rows and paste them to a new spreadsheet. The CTRL-END should work now since there aren't any remnants left from the deletion. You can save the sheet as the same name as the old one, but be sure to close the prior sheet or else it will be open and you won't be able to save to the same name. Another trick that might work is that after pressing CTRL-END, press END UP (not at the same time) and then you might end up at the last row of the sheet.
    0 pointsBadges:
    report
  • Woollysox
    Thanks Andymadge, that was useful. The Ctrl & Down arrow will certainly be of use. The more you know, the more you realise you dont know! Cheers John Woolliscroft
    0 pointsBadges:
    report
  • AndyPaul
    When you delete the rows... are you Deleting the Entire Row, or just the moving the cells up? If you move them up... it still keeps the signature of the entire former dataset. If you Delete Rows, then the Ctrl-End should work. However, if it does not, you could try to just use END and a the Down Arrow key... it will take to you the last cell with data before a blank. You could then END and Right Arrow to go to the "end point" ... then Shift-Ctrl-Home to select the entire set. Hope this helps.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following