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
norik

--
norik
-----------------------------------------------------------------------
norika's Profile: http://www.excelforum.com/member.php...nfo&userid=487
View this thread: http://www.excelforum.com/showthread.php?threadid=49633

arrange cell order
 

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" wrote in
message ...

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

arrange cell order
 

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 a
a1, the second is at a21, the third... (before running marco). if usin
insert or delete function, it may affect the absolute cell reference. i
there any ideas to 'move' upward rather insert or delete?

TIA

norik

--
norik
-----------------------------------------------------------------------
norika's Profile: http://www.excelforum.com/member.php...nfo&userid=487
View this thread: http://www.excelforum.com/showthread.php?threadid=49633