arrange cell order

hi,
i have some data as follows:

A B C
1 abc xyz 15 (as a group of data)
2 hij 20
3 qwe wer 17

i want a result
A B
1 abc 15
2 xyz
3 -space-
4 hij 20
5 -space-
6 qwe 17
7 wer

is there any ideas how to write marco to solve it?
TIA
norika

Try this......

Sub Shuffler()
Dim xlr As Long, xr As Long, xB

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

For xr = xlr To 1 Step -1
xB = Cells(xr, 2)
Cells(xr, 2) = Cells(xr, 3)
Cells(xr, 3) = ""
Rows(xr + 1).EntireRow.Insert shift:=xlDown
Cells(xr + 1, 1) = xB
If xr > 1 Then Rows(xr).EntireRow.Insert shift:=xlDown
Next

End Sub

--
Cheers
Nigel



"norika" <[email protected]> wrote in
message news:[email protected]...
>
> hi,
> i have some data as follows:
>
> A B C
> 1 abc xyz 15 (as a group of data)
> 2 hij 20
> 3 qwe wer 17
>
> i want a result
> A B
> 1 abc 15
> 2 xyz
> 3 -space-
> 4 hij 20
> 5 -space-
> 6 qwe 17
> 7 wer
>
> is there any ideas how to write marco to solve it?
> TIA
> norika
>
>
> --
> norika
> ------------------------------------------------------------------------
> norika's Profile:
http://www.excelforum.com/member.php...fo&userid=4878
> View this thread: http://www.excelforum.com/showthread...hreadid=496333
>

nigel,
thank you for your prompt reply.

after running your marco, the result is as below
A B
1 abc 15
2 xyz
3 space
4 hij 20
5 space ***
6 space ***
7 qwe 17
8 wer

is there any method to delete or erase line 6?

also, i have a lot ot sets of similar data. for example, set 1 is at a1, the second is at a21, the third... (before running marco). if using insert or delete function, it may affect the absolute cell reference. is there any ideas to 'move' upward rather insert or delete?

TIA

norika