Copying down, scroll speed

[Joe]

Hi all, I've got a problem when I copy down a formula by
dragging the corner of the first cell. I'm trying to put
it down only a certain distance, but it seems like it
either goes really slow and seems like it'll take hours,
or it jumps down thousands of lines at a time. Is there
some way to make it scroll down at speeds useful to
humans?
thanx

 

[Norman Harker]

Hi Joe!

Select the cell you want to copy
Copy
scroll down to where you want to end your copying but don't select it
Press and hold down Shift and select the last cell
Release Shift
Ctrl+V

If you are copying down parallel to a row of data:
Select the Cell
Point to bottom right to get the + sign
Double click.



But note that the + fill-handle at bottom right of a cell requires
enabling of the drag and drop option:



(I think it was Gord Dibben who introduced me to that one).



Tools > Options > Edit

Allow cell drag and drop check box is selected.

And thanks to Myrna Larson (MVP) for the following:



As for a shortcut for entering the formula, if it's in column A the
fill handle trick won't work unless B2:B1000 is already filled.



The following will work regardless of what's in the columns to the
right or left:



Assuming you typed the first formula in A2, Edit/Goto (or CTRL+G) and
type A2:A1000 in the Reference box. This will select A2:A1000, with A2
the active cell. Then press F2 to get into Edit mode and press
CTRL+ENTER. That will enter the formula in all selected cells (i.e.
through A1000).


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Robert Rosenberg]

Adding to Norm's response:

You can control the speed of your copy based on how far down the screen you
drag the mouse. Drag it so it's just touching the top part of a Worksheet
tab and it copies slowly but steadily. Drag it down to the bottom of the
Worksheet tab and it copies faster. Drag it as low as the Status Bar (or any
toolbar positioned between the Status Bar and the Worksheet Tabs) and it
copies faster still. Drag it below the Status Bar and it races down the
screen.

The same goes for copying across columns. The farther to the right you drag
your mouse the faster it copies. It can be tricky at first but I always drag
to the Worksheet Tab/Vertical Scroll Bar, then slowly move down/right until
I reach the speed I'm comfortable with.

One additional technique:

1. Select all of the cells first, by clicking the first cell, then using the
scroll bar locate the last cell and shift-click it (Norm mentioned this)

2. Once they're all selected, including the first cell containing the data
you want to copy, click the Formula Bar (white line above the sheet)
This switches Excel to the Edit mode and thinks you want to change the
first cell (which we're not going to do).

3. Press Ctrl+Enter on the keyboard
This copies the first cell to the other highlighted cells.

Note: If you're trying to copy multiple cells of data down multiple columns,
this technique will not work as desired. It always copies the first cell in
the selected area down AND to the right.
--
_______________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel
http://www.r-cor.com

 

[vm]

The easiest way to copy cells down is double-click
fillhandle in the bottom right corner of the active cell

VM

 

[Rune_Daub]

this only works though if you A:A column have a certain amount of cell
with data.. lets say it contains 500 lines.
If you in B2 have a value that you want to copy down... then do as told
by VM.. but it will only fill out down to the 500. line.
If you want to fill out more than the 500 lines, you will have to drag
it.

 

[Joe]

Thanks for the help everyone.
Double clicking the drag handle is working the best for
me because pretty much all of my columns are the same
height, so it puts it right where I need it to be.