Excel Formulae - copy to variable locations. 3

[Rybrookar]

I have a spreadsheet that uses imported data of variable length (always the same width though). The data normally goes from cell A2 to R3000 (somethimes greater than 3000, somethimes less.

In cell s2 I have a formula. I want to copy this formula to s3,s4 etc all the way down to s3000 (or whatever the length of the data is). After this I want to convert the result back to values to save space and make the spreadsheet smaller (in mb size).

I can convert to values I think but I'm having difficulty in getting the VB code to copy to a variable destination.

Any help would be gratefully appreciated.

 

[AnotherHiggins]

LastRow = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
Range("S2:S" & LastRow) = [blue]"=RC[-1]+RC[-15]"[/blue]

Replace the formula in blue with whatever your formula is. For help in getting the formula with RC reference, use the macro recorder (Tools > Macros > Record New Macro) and type your formula into any cell in solumn S, then observe the code.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlbo]

To get the last row of data:

dim lRow as long

lRow = cells(65536,1).end(xlup).row

You may use a variable in a range as such:

Range("A2:R" & lrow)

All you then need to do is COPY the cell with the formula in to the last row - tip - check out the DESTINATION argument of the COPY method

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

 

[Rybrookar]

Thanks very much - I used a bit of a combination and it works perfectly - the the following:


Range("S2:W2").Select
Selection.Copy
LastRow = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
Range("S2:S" & LastRow).Select
ActiveSheet.Paste
End Sub

 

[xlbo]

Just so you know - there is rarely a need to select:

LastRow = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row

Range("S2:W2").Copy destination:= Range("S3:W" & LastRow)



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