Need some help

  • Thread starter Thread starter Christopher Stern
  • Start date Start date
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
 
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)
 
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
 
Nice approach Gord, but declaring nocols as long gives an error when testing
for "". Needs to be declared as Variant.

Regards

Bob
 
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.
 
Back
Top