Filling down

  • Thread starter Thread starter GB
  • Start date Start date
G

GB

I produce lots of spreadsheets with calculations with say sixty rows in. I
then move to the column on the right hand side, put a formula in the top
cell, and I want to copy that down so it fills up the same number of rows as
the column next door.

I have tried all sorts of fancy keystroke combinations, but none of them
work.

Anybody know the magic keystrokes, please?

Thanks

Geoff
 
GB said:
I produce lots of spreadsheets with calculations with say sixty rows
in. I then move to the column on the right hand side, put a formula
in the top cell, and I want to copy that down so it fills up the same
number of rows as the column next door.

I have tried all sorts of fancy keystroke combinations, but none of
them work.

Anybody know the magic keystrokes, please?
Click to expand...

Left arrow to a populated column, ctrl-down arrow to the bottom of the
column, right arrow to the bottom of the unpopulated column, ctrl-shift-up
arrow, tweak using down arrow if necessary, fill down. It's easier than it
sounds. I think it used to be even easier (i.e. one keystroke). I also
like Gord's solution, but I like keyboarding better than mousing.

Dave
dvt at psu dot edu
 
Hi GB.

I don't do code, so I decided to tangle with VBA for beginners in MSKB. I
think this successfully takes out all absolute references. I tested it with
1,2,3 in A1:A3 and =A1*2 in B1. Worked OK, so I dragged A1:A3 and B1 to
various other locations and still seemed to work.

If it's wrong, someone'll shred it and then you'll have an answer.

Rgds,
Andy

NB - watch for text wrap ; 2nd line should be continuous.

n = ActiveCell.Row
ActiveCell.Copy Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(65536 -
n, -1).End(xlUp)).Offset(0, 1)
 
Can't think why it didn't occur to me to look at working in xlDown. Seems to
work fine. It's probably more efficient than xlUp (?which might make XL
check how many rows there are in a column every time?). Plus it would make
it version-proof.

Well done, I like it.

Rgds,
Andy
 
Can't think why it didn't occur to me to look at working in xlDown.

Unless it was subconcious. xlDown will of course baulk at blank cells in the
range. But for your purposes that shouldn't be a problem.

Rgds,
Andy
 
Andy Brown said:
Can't think why it didn't occur to me to look at working in xlDown. Seems to
work fine. It's probably more efficient than xlUp (?which might make XL
check how many rows there are in a column every time?). Plus it would make
it version-proof.

Well done, I like it.

Rgds,
Andy
Click to expand...
Thank you very much.
 
Back
Top