Cut and Paste to a Variable Range - Excel VBA 1

[Roonaldez]

Hi,

new to Excel vba and struggling with a copy paste function...I have a list of values, in column A, which has a variable range but a fixed start point...so could be a2:a30 or a2:a22....I then have a line of formulae in b2:h2 which I would like to copy and paste for the number of rows that is being used in column a...I have tried to understand the range function, and I have recorded the action of dragging the formulae down, but it doesn;t allow for a variable range....Any ideas?

Thank you

 

[ck1999]

Sub test()
a = Range("a2").End(xlDown).Row
Range("b2:h" & a).AutoFill Destination:=Range("b3:h" & a), Type:=xlFillDefault
End Sub

ck1999

 

[xlbo]

Have a look at the FAQs - there are at least 2 that deal with finding the last used cell....

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

 

[ck1999]

Sorry for this mistake change
Range("b2:h" & a).AutoFill Destination:=Range("b3:h" & a), Type:=xlFillDefault

to

Range("b2:h2").AutoFill Destination:=Range("b2:h" & a), Type:=xlFillDefault


Hope this helps

ck1999