Reading Rows of Variable Length

[DCondie]

Working in Excel 2003

I am trying to read the contents of cells in a row of variable length
offset by the contents of cell C2 and write the contents to Sheet 2
starting at cell C2.

I have written the following procedure to accomplish the task:

Sub CopyProcedure()
Dim I As Integer
I = 2

Do
With Worksheets("Sheet1").Cells(I, 2)
.Range(.Cells(1), _
.End(xlToRight)).Copy Destination:=Worksheets("Sheet2").Cells(I,
2)
I = I + 1
End With
Loop While Worksheets("Sheet1").Cells(I, 2).Value <> ""
End Sub

The problem:
The procedure is reading and writing the next row down from the
initial offset cell (C2). Thereafter, in the looping process it reads
and writes the contents of the second row after the row just written.

How do I get the procedure to read and write every row?

 

[JE McGimpsey]

One way:

Public Sub Copy2()
Dim i As Long
With Sheets("Sheet1")
For i = 2 To .Cells(.Rows.Count, 3).End(xlUp).Row
.Range(.Cells(i, 3), .Cells(i, 3).End(xlToRight)).Copy _
Destination:=Sheets("Sheet2").Cells(i, 3)
Next i
End With
End Sub

 

[Bob Phillips]

Here's an alternative

Dim I As Integer
I = 2

Do
With Worksheets("Sheet1").Cells(I, 3)
.Resize(1, .End(xlToRight).Column - .Column).Copy _
Destination:=Worksheets("Sheet2").Cells(I, 3)
I = I + 1
End With
Loop While Worksheets("Sheet1").Cells(I, 3).Value <> ""


--

HTH

RP
(remove nothere from the email address if mailing direct)