A hard DO loop for me

B

bert

Fellow coders,

I have large files of data where I need to
copy every other row alongside the initial
row, and then delete the rows that are
copied.

An example of the procedure's code, without
ANY loops whatsoever follows:

Range("B3:K3").Select
Selection.Copy
Range("L2").Select
ActiveSheet.Paste
Range("B5:K5").Select
Application.CutCopyMode = False
Selection.Copy
Range("L4").Select
ActiveSheet.Paste
Range("A7:K7").Select
Application.CutCopyMode = False
Selection.Copy
Range("L6").Select
ActiveSheet.Paste
Range("3:3,5:5,7:7").Select
Range("A7").Activate
Selection.Delete Shift:=xlUp

I absolutely cannot do this without loops,
as the code will exceed the maximum allowable
lines.

How do I set the DO loop variables to do this
kind of incremental, every other row, rearrange,
and then delete?

Thanks,

Bert Gold
 
B

Bob Phillips

Assuming that A7:K7 should be B7:K7, then

Dim i As Long

For i = 6 To 2 Step -2
Range("B" & i + 1 & ":K" & i + 1).Copy Destination:=Range("L" & i)
Range("B" & i + 1).EntireRow.Delete
Next i


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

Tushar Mehta

*What* are you doing?

Each paste is partially overwriting the result of the previous paste
operations.

Also, for the first two you are copying B:K, but for the third the
range is A:K.

You might want to reexamine what exactly you want to do.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
G

Greg Wilson

I think I would forget the selecting and copying and just
transfer the values and delete the rows. Note that you
referenced B3:K3, B5:K5 and then A7:K7 (???). I assumed
this was a typing error.

Sub XXX()
Dim i As Long
For i = 2 To 4 (change to suit)
Range(Cells(i, 12), Cells(i, 21)).Value = _
Range(Cells(i + 1, 2), Cells(i + 1, 11)).Value
Rows(i + 1).EntireRow.Delete
Next

Regards,
Greg
 
A

Alan Beban

Fellow coders,

I have large files of data where I need to
copy every other row alongside the initial
row, and then delete the rows that are
copied.
. . .
Click to expand...
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook:

Range("L2").Resize(Range("B2:K7").Rows.Count \ 2, 10).Value
= _ ArrayAlternates(Range("B2:K7"), False)

Alan Beban
 
Top