A little more general than you indicated, but less prone
to errors in that it does not check for even/odd rows,
and can be rerun without destroying data.

Starting from the bottom, because that is what you do when
inserting or deleting rows so you don't stumble all over yourself..

If there is a value in A, nothing else in the row, and no value in F of the row above
then the cell in A will be placed in column F of the row above
and the row will be deleted.

Sub Join_Overflowed_A()
'-- Merge cells in A w/o other values on Row to row above w/o F
' D.McRitchie, 2008-02-23 in excel.newusers
Dim LastRow As Long
Dim I As Long
LastRow = Cells.SpecialCells(xlLastCell).Row
For I = LastRow To 2 Step -1
If Not IsEmpty(Cells(I, 1)) And Cells(I - 1, 6) = "" _
And Application.CountA(Cells(I, 1).EntireRow) = 1 Then
Cells(I - 1, 6) = Cells(I, 1).Formula
Cells(I, 1).EntireRow.Delete
End If
Next I
done:
End Sub
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"PA" wrote in message ...
I have a data file imported from a AS400 down load and it is not coming in
neatly.
All the even rows, starting at row 2, use columns A.B,C,D,and E.
Odd rows use column A
We are using several hundred rows.
How can I move the data from each odd row to Column F in the row immediately
above.
THis is an on going event and the number of rows may vary from one week to
another.

Thank you