Copying Data - Simple for you experts!
Posted by Barry Lindfield on March 22, 2001 12:54 PM
Please help.
I've got data in say columns A and B which need to be summed in column C.
When I try and record the macro it always assigns a specific range to column c (i.e. C1:C989).
My problem is that the lengths of a and b vary from week to week.
Is there a way to resolve this.
Any help much appreciated.
Posted by Barrie Davidson on March 22, 2001 1:55 PM
You need to identify the last row in the range, recording a macro won't work because it records the range you sum up. A quick way to identify the last row is declare a variable (say Last_Row) and assign the last row number to that variable. The syntax is:
Last_Row = ActiveSheet.UsedRange.Rows.Count
Assuming you want to sum columns A and B in column C for each row, you would then put the following in your code:
Range("C1").FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
Range("C1").Copy
Range("C1:C" & Last_Row).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C1").Select
Barrie.
Posted by Barry Lindfield on March 22, 2001 2:13 PM
Many thanks Barrie your solution worked a treat!!
