Concatanate odd rows

  • Thread starter Thread starter Yogi_Bear_79
  • Start date Start date
Y

Yogi_Bear_79

I need to concatanate the odd rows to the ends of the even rows. Starting at
row 2. I'd like the filled cells from row 2 to be added to the first empty
cell in row 1. I'd like this proces to continue to the end of the
spreadsheet.

Once completed I should have 1/2 the original rows
 
if you had data in column A, B, C and D on both rows 1 and 2, then after
this was done, would row 1 have data in

A B C D E F G H where E would be row2, column A data, F would be
row2, column B data and so forth.

Also, is all data constants or are there formulas involved - if formulas, do
you want the data transferred as constants?

can we assume all the data is contiguous in a row or are the blank cells
embedded between filled cells - if so, do you want the blank cells skipped
or reproduce to maintain column alignment.
 
The data in it's original for fills columns A~K. The Data in rows 1 & 2 need
to be combined into one row. Resulting in a row with data in columns A~T. I
mis-spoke on the original post. I need the data in row 2 concatantaed to the
end of row 1.

The data is constant. There are no formulas. THe data is retrived from a
poorly writen database that can only export to a pdf. Once in the pdf we can
use OmniPage to import the data into Excel.

There can be some blank cells, I would like them reproduced to maintain
column alignment. I cna do the task manually, so once automated, I can
trouble shoot the results to see if it needs tweeked.
 
How can they all be the same size? A~K is eleven columns; L~T is nine.
If they are all A~K, then concatenated they would be A~V.

Alan Beban
 
Sub MoveData()
Dim rng As Range
Dim data As Range
Dim i As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
If rng.Row Mod 2 = 1 Then _
Set rng = rng.Offset(1, 0)
For i = rng.Row To 2 Step -2
Set data = Cells(i, 1).Resize(1, 11)
data.Copy Destination:=Cells(i - 1, 12)
data.EntireRow.Delete
Next
End Sub
 
Assuming your last row of data is Row n (an even numbered row) on the
4th sheet, and you want to copy the concatenated data to the 5th sheet,
and assuming further you meant A~V rather than A~T, then if the
functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook:

Set rng = Sheets(4).Range("a1:k" & n)
Sheets(5).Range("A1:V" & n / 2).Value = ArrayReshape(rng, n / 2, 22)

Alan Beban
 
Back
Top