MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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!!