Copy a range of cells 15 rows down and repeat to empty cell

's Computer Specifications

I have Excel 2003 and Windows XP.
My spreadsheet is currently 8,240 lines. I want to copy a range (containing
both absolute and relative cell references) of 15 lines and copy the range
down the 8,000 lines. The code I've tried is:

Sub loop1()
'this loop runs until there is nothing in the next column
Do
Range(Range("K7:R21"), Range("k7:R21").End(xlDown)).Copy Range("K22")
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

It copies one line at a time and bogs down in fairly short order.

--
M.R. Vanatta

's Computer Specifications

On 5 Jun., 23:05, Mike V <Mi...@discussions.microsoft.com> wrote:
> I have Excel 2003 and Windows XP.
> My spreadsheet is currently 8,240 lines. *I want to copy a range (containing
> both absolute and relative cell references) of 15 lines and copy the range
> down the 8,000 lines. *The code I've tried is:
>
> Sub loop1()
> 'this loop runs until there is nothing in the next column
> Do
> Range(Range("K7:R21"), Range("k7:R21").End(xlDown)).Copy Range("K22")
> ActiveCell.Offset(1, 0).Select
> Loop Until IsEmpty(ActiveCell.Offset(0, 1))
> End Sub
>
> It copies one line at a time and bogs down in fairly short order.
>
> --
> M.R. Vanatta

Hi

Which cell is the ActiveCell at the start of the macro, ie which
column is used to determine last row to copy to?

Regards,
Per

's Computer Specifications

The active cell is K7 when the macro starts.
--
M.R. Vanatta

"Per Jessen" wrote:

> On 5 Jun., 23:05, Mike V <Mi...@discussions.microsoft.com> wrote:
> > I have Excel 2003 and Windows XP.
> > My spreadsheet is currently 8,240 lines. I want to copy a range (containing
> > both absolute and relative cell references) of 15 lines and copy the range
> > down the 8,000 lines. The code I've tried is:
> >
> > Sub loop1()
> > 'this loop runs until there is nothing in the next column
> > Do
> > Range(Range("K7:R21"), Range("k7:R21").End(xlDown)).Copy Range("K22")
> > ActiveCell.Offset(1, 0).Select
> > Loop Until IsEmpty(ActiveCell.Offset(0, 1))
> > End Sub
> >
> > It copies one line at a time and bogs down in fairly short order.
> >
> > --
> > M.R. Vanatta
>
> Hi
>
> Which cell is the ActiveCell at the start of the macro, ie which
> column is used to determine last row to copy to?
>
> Regards,
> Per
>

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Copy Values to next empty cell range	Aldo Cella	Microsoft Excel Worksheet Functions	1	10th Mar 2008 11:22 PM
Fill empty cells in a range using the used cell	=?Utf-8?B?YmRtc2Zhbg==?=	Microsoft Excel Programming	1	6th Dec 2006 08:52 AM
Macro to copy cells one row up then move down 4 rows & repeat	=?Utf-8?B?Sm9zZWY=?=	Microsoft Excel Programming	4	5th Oct 2004 08:49 PM
Re: VBA to copy to empty cell directly below a cell when analogous cells in different column have same value as each other?	Steven Rosenberg	Microsoft Excel Programming	0	5th Aug 2003 06:10 AM
VBA to copy to empty cell directly below a cell when analogous cells in different column have same value as each other?	SROSENYC	Microsoft Excel Programming	1	5th Aug 2003 04:34 AM