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