Inserting blanks or changing dimensions

[Jones]

I have a list, i.e. a one dimensional array, of numbers. I want to
insert 9 blanks between each cell that has a number in it. Is there a
simple way to do this? Or in the alternative, is there an easy way to
convert a two dimensional array into a one dimensional array (that is,
for my purposes, to insert a 9 blank rows below my one dimensional
array of data & then convert it to a one dimensional array, thereby
leaving nine blanks between each filled cell)?

 

[Guest]

Let's say we have data in column A. It does not matter if the data is
contiguous or not. The following small macro will:

1. gather the data in column A
2. clear column A
3. re-enter the data with fixed spacing


Sub space_it()
Dim l As Long
Dim a(7000)
Dim r As Range

Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
k = 1

For i = 1 To nLastRow
If IsEmpty(Cells(i, 1).Value) Then
Else
a(k) = Cells(i, 1).Value
k = k + 1
End If
Next

Columns("A:A").Clear

For i = 1 To k
j = 10 * i - 9
Cells(j, 1).Value = a(i)
Next

End Sub