Need some help

C

Christopher Stern

I am looking to do the following:

I want a solution or macro which will do the following:

I have data in column one but need the every other line to be cut and
pasted in column 2 one after the other then need column one empty
spaces to be deleted so data will show after the other which
corresponds with column 2.

Example:

How data comes thru now:

Data 1
Data 2
Data 1
Data 2

How I want data to show:
Data 1 Data 2
Data 1 Data 2

Thanks,
Chris
 
B

Bob Phillips

Christopher,

This should do it

Sub CutRows()
Dim i As Long
Dim cLastRow As Long

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
cLastRow = cLastRow - cLastRow Mod 2 'cater for odd row

For i = cLastRow To 2 Step -2
Cells(i - 1, "B").Value = Cells(i, "A").Value
Cells(i, "A").EntireRow.Delete
Next i

End Sub


--

HTH

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

Gord Dibben

Chris

Sub ColtoRows()
Dim rng As Range
Dim i As Long
Dim j As Long
Dim nocols as Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
nocols = InputBox("Enter Number of Columns Desired")
If nocols = "" Or Not IsNumeric(nocols) Then Exit Sub
For i = 1 To rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(rng.Row, "A")).ClearContents

End Sub

Gord Dibben Excel MVP
 
B

Bob Phillips

Nice approach Gord, but declaring nocols as long gives an error when testing
for "". Needs to be declared as Variant.

Regards

Bob
 
K

Ken Wright

I know this is the programming group, but you did say a solution or code, and as
you have the code, why not have another avenue as well:-

With your data in say A1:A1000, in cell B1 put the following formula, copy it
and then paste to B1:C500

=OFFSET($A$1,ROW()*2-(4-COLUMN(B1)),0)

When done, simply select B1:C500, copy and paste special as values and then
delete Col A.
 
Top