All data is kept in a single column, the number of rows varies (up to 2ooo)

How to move the cells in col. A:A next to the higher cell in Col. B:B?

Example:

A2 goes to B1 (row 2 thereby is empty now)
A4 goes to B3 (row 4 thereby is empty now)
A6 goes to B5 (row 6 thereby is empty now)
etc.

I´m sure there is a better way than record a macro that does it one by one.
(And where to stop?)

Here's a macro that cuts everything from the currently select cell down to
the first blank and move it one cell right and one cell up:
Sub MoveRightUp()
Range(ActiveCell,Activecell.End(xlDown)).Cut ActiveCell.Offset(-1,1)
End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"Tatebana" wrote:

All data is kept in a single column, the number of rows varies (up to 2ooo)

How to move the cells in col. A:A next to the higher cell in Col. B:B?

Example:

A2 goes to B1 (row 2 thereby is empty now)
A4 goes to B3 (row 4 thereby is empty now)
A6 goes to B5 (row 6 thereby is empty now)
etc.

I´m sure there is a better way than record a macro that does it one by one.
(And where to stop?)

Sub tatebana()
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To n Step 2
Cells(i - 1, 2).Value = Cells(i, 1).Value
Cells(i, 1).Clear
Next
End Sub

--
Gary''s Student
gsnu200712


"Tatebana" wrote:

All data is kept in a single column, the number of rows varies (up to 2ooo)

How to move the cells in col. A:A next to the higher cell in Col. B:B?

Example:

A2 goes to B1 (row 2 thereby is empty now)
A4 goes to B3 (row 4 thereby is empty now)
A6 goes to B5 (row 6 thereby is empty now)
etc.

I´m sure there is a better way than record a macro that does it one by one.
(And where to stop?)

Thank you Charles,

I wrote the following:

On running it I got a Run-Time Error 1004: "Application defined or Object
defined Error"

Where did I mess up?



"Charles Chickering" wrote:

Here's a macro that cuts everything from the currently select cell down to
the first blank and move it one cell right and one cell up:
Sub MoveRightUp()
Range(ActiveCell,Activecell.End(xlDown)).Cut ActiveCell.Offset(-1,1)
End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"Tatebana" wrote:

All data is kept in a single column, the number of rows varies (up to 2ooo)

How to move the cells in col. A:A next to the higher cell in Col. B:B?

Example:

A2 goes to B1 (row 2 thereby is empty now)
A4 goes to B3 (row 4 thereby is empty now)
A6 goes to B5 (row 6 thereby is empty now)
etc.

I´m sure there is a better way than record a macro that does it one by one.
(And where to stop?)

I wrote the following little piece

n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To n Step 2
Cells(i - 1, 2).Value = Cells(i, 1).Value
Cells(i, 1).Clear
Next
Columns("B:C").Select
Columns("B:C").EntireColumn.AutoFit
Range("D1").Select

End Sub

When runing it, it jumps imediately to the "Next" line


"Gary''s Student" wrote:

Sub tatebana()
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To n Step 2
Cells(i - 1, 2).Value = Cells(i, 1).Value
Cells(i, 1).Clear
Next
End Sub

--
Gary''s Student
gsnu200712


"Tatebana" wrote:

All data is kept in a single column, the number of rows varies (up to 2ooo)

How to move the cells in col. A:A next to the higher cell in Col. B:B?

Example:

A2 goes to B1 (row 2 thereby is empty now)
A4 goes to B3 (row 4 thereby is empty now)
A6 goes to B5 (row 6 thereby is empty now)
etc.

I´m sure there is a better way than record a macro that does it one by one.
(And where to stop?)

Make sure column A is not empty
--
Gary''s Student
gsnu200712


"Tatebana" wrote:

I wrote the following little piece

n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To n Step 2
Cells(i - 1, 2).Value = Cells(i, 1).Value
Cells(i, 1).Clear
Next
Columns("B:C").Select
Columns("B:C").EntireColumn.AutoFit
Range("D1").Select

End Sub

When runing it, it jumps imediately to the "Next" line


"Gary''s Student" wrote:

Sub tatebana()
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To n Step 2
Cells(i - 1, 2).Value = Cells(i, 1).Value
Cells(i, 1).Clear
Next
End Sub

--
Gary''s Student
gsnu200712


"Tatebana" wrote:

All data is kept in a single column, the number of rows varies (up to 2ooo)

How to move the cells in col. A:A next to the higher cell in Col. B:B?

Example:

A2 goes to B1 (row 2 thereby is empty now)
A4 goes to B3 (row 4 thereby is empty now)
A6 goes to B5 (row 6 thereby is empty now)
etc.

I´m sure there is a better way than record a macro that does it one by one.
(And where to stop?)