Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A hard DO loop for me
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A hard DO loop for me
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) wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
A hard DO loop for me
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 -----Original Message----- 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
A hard DO loop for me
wrote:
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. . . . 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Too hard to do? | Charts and Charting in Excel | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
Worksheet_Change - loop within a loop | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming | |||
Is this really hard? | Excel Programming |