macro for cutting immediate cell below and pasting adjacent

[sheetspread]

Say I have:

Red
Orange
Yellow
Green
Blue
Indigo
Violent

in a column (no other text)

and want to cut the cell immediately below, then paste it next to the one above it:

Red Orange
Yellow Green
Blue Indigo


.......yadda yadda

What code should I use?

 

[erik.van.geit]

Hi,

try this

Option Explicit

Sub test()
Dim rng As Range
Dim arr As Variant
Dim LR As Long
Dim i As Long

LR = Cells(Rows.Count, 1).End(xlUp).Row

Set rng = Range("A1:A" & LR)
arr = rng

    For i = 2 To LR Step 2
    arr(i / 2, 1) = arr(i - 1, 1) & " " & arr(i, 1)
    Next i
    If LR Mod 2 = 1 Then arr(i / 2, 1) = arr(i - 1, 1)

rng = arr
Range("A" & Int(LR / 2) + 1 + LR Mod 2 & ":A" & LR).ClearContents

Erase arr
End Sub

for more info about this technique see http://puremis.net/excel/code/053.shtml

kind regards,
Erik

 

[sheetspread]

Whoa that's wild

Thanks Erik, you know your code

 

[erik.van.geit]

Re: Whoa that's wild

Thanks Erik, you know your code

You're WELCOME

in fact it's knowing some code, taken from helpfiles or internet
+
some trial and error (based on mathematical logic)

you too can find this kinda stuff now