Inserting blanks or changing dimensions
 

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)?

Inserting blanks or changing dimensions
 

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
--
Gary''s Student


"Jones" wrote:

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)?