Excel Visual Basic - Copy Down Function

[DanAuber]

Hi there,

in Excel if I'm in a cell which is adjacent to a column with data in it, if I hover the mouse over the bottom right corner (so I get a black cross) and double-click - the cell I'm in copies its formula down until it gets to the end of data in the column adjacent. Is there a way to replicate this in Visual Basic ?

Thanks for any help

Dan

 

[ck1999]

try something like


Sub test()
vrow = ActiveCell.Row
vcol = ActiveCell.Column

Range(Cells(vrow + 1, vcol), Cells(Cells(vrow, vcol - 1).End(xlDown).Row, vcol)) = Cells(vrow, vcol).Formula
End Sub


ck1999

 

[xlbo]

...or try recording yourself doing it and looking at the code that is generated....

Whjat you are after is the AUTOFILL method of the range object

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DanAuber]

Thanks for this Geoff - I didn't have a problem recording it - however the tricky bit was that I couldn't predict the length of my data each time which is why something similar to what ck1999 has suggested does the trick

dan

 

[xlbo]

There are 2 FAQs for finding the last row of data in this forum. Eiter ck's way or using autofill doesn't really matter - the crux is substituting a variable for your end row:

FAQ707-2112
faq707-2115

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question