Break apart in long excel spreed sheet
 

I have one excel spread sheet like this on ColumnA like this
000
001
002
003
004
005
..
..
..
all the way done real long Column A
I would like to break down every 16 rows, so the
A1 will show000
A16 will show 001
A32 will show 002
A48 will show.003
so on and so for, until to the end of row

Thanks.

Lillian

Break apart in long excel spreed sheet
 

Lillian:

try it,

r = Range("A65536").End(xlUp).Row
For i = r To 2 Step -1
Rows(i & ":" & i + 15).Insert Shift:=xlDown
Next

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Lillian" wrote:

I have one excel spread sheet like this on ColumnA like this
000
001
002
003
004
005
.
.
.
all the way done real long Column A
I would like to break down every 16 rows, so the
A1 will show000
A16 will show 001
A32 will show 002
A48 will show.003
so on and so for, until to the end of row

Thanks.

Lillian

Break apart in long excel spreed sheet
 

SAVE your data first then try:

Sub mvdata()
Dim theData As Variant
Dim i As Long
theData = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
If UBound(theData) < 4098 Then
Columns(1).ClearContents
Columns(1).NumberFormat = "@"
Range("A1").Value = theData(1, 1)
For i = 2 To UBound(theData)
Cells((i * 16) - 16, 1).Value = theData(i, 1)
Next i
Else
MsgBox "Too much data"
End If
End Sub

Hope this helps
Rowan

Lillian wrote:
I have one excel spread sheet like this on ColumnA like this
000
001
002
003
004
005
.
.
.
all the way done real long Column A
I would like to break down every 16 rows, so the
A1 will show000
A16 will show 001
A32 will show 002
A48 will show.003
so on and so for, until to the end of row

Thanks.

Lillian

Break apart in long excel spreed sheet
 

It works, thanks

"Lillian" wrote:

I have one excel spread sheet like this on ColumnA like this
000
001
002
003
004
005
.
.
.
all the way done real long Column A
I would like to break down every 16 rows, so the
A1 will show000
A16 will show 001
A32 will show 002
A48 will show.003
so on and so for, until to the end of row

Thanks.

Lillian

Break apart in long excel spreed sheet
 

Thanks It work, what is mean by " r To 2 Step -1" and "Rows(i & ":" & i +
15).Insert Shift:=xlDown"

Can you explain to me, I will be really appreciate it.

"chijanzen" wrote:

Lillian:

try it,

r = Range("A65536").End(xlUp).Row
For i = r To 2 Step -1
Rows(i & ":" & i + 15).Insert Shift:=xlDown
Next

--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"Lillian" wrote:

I have one excel spread sheet like this on ColumnA like this
000
001
002
003
004
005
.
.
.
all the way done real long Column A
I would like to break down every 16 rows, so the
A1 will show000
A16 will show 001
A32 will show 002
A48 will show.003
so on and so for, until to the end of row

Thanks.

Lillian