Need some help from super Excel 2007 gurus

[Ilmater]

OK, I don't know how much other people know about the feature Microsoft put in this new version that pertains to selecting large sections of cells. I work with Excel almost all day, so I know it's in there, though I haven't heard anything about it and can't find it anywhere online.

Example: I have a tab with 20 columns and 40,000 rows, surrounded with a border. Now, let's say I select the 20 columns in the first row and drag the box in the bottom-right corner down until I get to the bottom of the 40k rows to copy the formulas (or values) all the way down. Once I drag down to the bottom of what I can see on the screen, the vie starts scrolling downwards pretty fast. This version of Excel - as opposed to 2003 - will slow down that scrolling when it gets really close to the bottom of the section I have the border around.

Now, this is probably a feature most people don't even recognize, and I can't see Microsoft even mentioning it, as I've never seen it mentioned as a feature. But again, I work with this all day long, and I can tell you it's there. My problem is, IT'S A FUCKING DISASTER! It does not work, and I just do not like it slowing me down. I will figure out for myself when to stop scrolling a page. Also, it works sometimes when it thinks I'm trying to select a large area, and doesn't work other times, so I always have to act as though it's NOT going to happen, so it's fucking annoying when it starts slowing down the scroll when I want to keep going.

Can someone, anyone, please tell me if I can turn this off? It's driving me insane, and I'm going to throw my monitor out the window if it doesn't stop.

 

[Crusty]

Why not just record a macro that does the selection and run that every time instead of manually doing the copying?

 

[Ilmater]

Why not just record a macro that does the selection and run that every time instead of manually doing the copying?

That would only work if we were talking about the same spreadsheet and the same section every time. I'm talking about spreadsheets I create from, say, an Access query and I use for a few minutes and then throw away. I'm talking about every single time I select data in any spreadsheet.

By the way, if anyone else at least sees this issue, please respond and let me know so that I know for sure I'm not crazy. I'm 100% sure it exists, but I wonder if I'm the only one that realizes it.

 

[NeoV]

hold the shift key

press the end key, then press the home key

that should highlight your entire set of data

also, I always turn transition navigation keys on in the options menu

that lets you use the end and arrow keys a little more logically

hold shift, hit the end key, then hit the down arrow key - assuming you have no blank rows, that will take you to the bottom of your highlighted set - you can use the same trick to now highlight to the right, getting all of the columns you want

 

[Ilmater]

hold the shift key

press the end key, then press the home key

that should highlight your entire set of data

also, I always turn transition navigation keys on in the options menu

that lets you use the end and arrow keys a little more logically

hold shift, hit the end key, then hit the down arrow key - assuming you have no blank rows, that will take you to the bottom of your highlighted set - you can use the same trick to now highlight to the right, getting all of the columns you want

Yeah, I know about those shortcuts, but I frequently have breaks in the data.

Again, I'm not talking about any one spreadsheet or one scenario, I'm talking about constantly being annoyed by the stupid way it tries to GUESS when I want the screen to slow down.

 

[Zorba]

Of course you know the CTRL+Shift+Down, CTRL+Shift+Over. I have noticed that excel 2007' scroll while selecting is much slower than it was in 2003. I haven't noticed this specific issue, I'll try it tomorrow at work and report back.

You could create a generic macro and save it to your personal spreadsheet. Then you would have it anytime you were on that computer.

 

[Ilmater]

Of course you know the CTRL+Shift+Down, CTRL+Shift+Over. I have noticed that excel 2007' scroll while selecting is much slower than it was in 2003. I haven't noticed this specific issue, I'll try it tomorrow at work and report back.

You could create a generic macro and save it to your personal spreadsheet. Then you would have it anytime you were on that computer.

Yeah, if you notice that if you drag a selection off to the side or bottom, it will slow down when you get to the edges. Very annoying.

 

[Edgy]

Create artificial row (just below last row) and artificial column (just to the right of last column) and fill them with arbitrary data (so there is no breaks) such as "1" --> select with CTRL+Shift+Down, CTRL+Shift+Over then deselect last column, deselect last row... hassle but quicker than scroll highlighting...

Alternatively, when I mass select with some breaks in data - I do it from bottom right most cell and use keyboard to highlight-select to A1 cell, which in many cases allows quick keyboard select ignoring the breaks in data.

 

[Ilmater]

Create artificial row (just below last row) and artificial column (just to the right of last column) and fill them with arbitrary data (so there is no breaks) such as "1" --> select with CTRL+Shift+Down, CTRL+Shift+Over then deselect last column, deselect last row... hassle but quicker than scroll highlighting...

Alternatively, when I mass select with some breaks in data - I do it from bottom right most cell and use keyboard to highlight-select to A1 cell, which in many cases allows quick keyboard select ignoring the breaks in data.

I do appreciate the thoughts, but I'm talking about adjusting many spreadsheets that I don't format before they get to me, and I have to manipulate them. I'm not adding a bunch of rows all over the place so that I can select things. Plus, many times I need to keep the data organized, so I can't just throw rows of garbage data all over the place.

Nothing is going to fix this unless there's a way to turn off this stupid scrolling thing.

 

[NeoV]

did you try

Hold the Shift key down, then

press and release the end key, then press and release the home key

that should highlight your entire set of data

That doesn't care if there are breaks in the data or not

 

[Zorba]

Holding shift+page down would probably go quite a bit faster than mouse scrolling also.

 

[Zorba]

BTW: I am at work now and I see exactly what you are talking about with the slow down towards the end of the selection. I can see how that could be very annoying. It doesn't do it if you hold down page-down instead of mouse scrolling. I seriously doubt there is an option for it. You might be able to turn it off with VBA, with something like Application.PredictiveScrolling = False, but I am not sure what the proper method to call is or if one even exists.

 

[Ilmater]

did you try

Hold the Shift key down, then

press and release the end key, then press and release the home key

that should highlight your entire set of data

That doesn't care if there are breaks in the data or not

That's an interesting shortcut I actually didn't know about, but again, it really doesn't fix the problem. Here's an example of why:

Let's say I'm making a forecast and I have 20 categories across the left and 24 months across the top... 11 months this year and 13 months for December and all of 2010. A1 is empty, while A2-A21 have categories... B1-Y1 have months in them. I will put a formula for each category in January, (cells B2-B21) and then I'll want to select JUST THOSE cells and drag those formulas across to column L... then I'll type in new formulas for my forecast into M2-M21 and I'll want to select JUST THAT COLUMN (not including the date or any columns to the left) and drag it across to column Y.

That is essentially the type of stuff I'm talking about, except for the fact that the data could be anywhere in the spreadsheet, and I usually deal with 10 - 12 different tables in the same worksheet, and they're 50x the size of the table in my example; so, I don't know where they'll be to write a macro, and I can't just select EVERYTHING or I'll be dragging things I don't want to drag.

THANK YOU ZORBA! It's good to hear that someone else notices it. It's f-ing annoying. I can't stand it. I do use the keyboard when I can, but think about selecting 3 and a half "screens" (by screen I mean the viewable number of cells on my screen at any one time) of data... I've got to either hold shift and page down a couple times and then manually click the arrow keys until I get right to the edges... also, many times I have to move right a long way, and as far as I know there's no keyboard shortcuts for paging right-to-left.

 

[GrumpyMan]

Control/End keys

 

[Edgy]

If you know the cell range you need to highlight - you can use the namebox to highlight them by specifying the range (example B2:B21 will highlight that range of cells)... not sure if this helps.

 

[techfuzz]

http://office.microsoft.com/en-us/excel/HP100738481033.aspx