row argument query...

  • Thread starter Thread starter jaimetimbrell
  • Start date Start date
J

jaimetimbrell

Dear All,

In my (recorded) macro there is this bit...

Range("Y5:AB5").Select
Selection.Copy
Range("Y6").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Paste

this copies the formulae down to the last active cell and pastes
them...this is fine and is the only way I could think how to do
it...but i only want to paste down as far as the number of rows in the
adjacent "X" column next to the Range("Y5:AB5")...

The practical difference is pasting down only 200 or so rows as opposed
to the 3000 rows and therefore a considerable time/file size saving...

...I have tried to look up various solutions but I am struggling...

Jaime
(LVP!)
 
Thanks for replying curlydave but I am afraid you are incorrect...

By holding down shift-ctrl-end, I create a paste range that is recorded
in Macro 1 as follows...

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

...that is to say it goes to the last row/column junction, which means
my equations are subsequently pasted down many 000's of rows, which I
want to avoid - but I dont know how to program in VBA so that it only
pastes down the number of rows in the 'X' column...

:(
 
Thanks for replying curlydave but I am afraid you are incorrect...

By holding down shift-ctrl-end, I create a paste range that is recorded in Macro 1 as follows...

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

...that is to say it goes to the last row/column junction, which means my equations are subsequently pasted down many 000's of rows, which I want to avoid - but I dont know how to program in VBA so that it only pastes down the number of rows in the 'X' column...

:(
 
no probs curlydave, thanks for your continued interest...

column X varies from spreadsheet to spreadsheet, so I am afriad I cant just fix a range...:(

I would like some kind of statement that calcs the number of rows in X and then uses that to copy down that many rows in the adjacent columns - any idea?

Regards,
 
Back
Top