Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
font size auto changing from 11 to 15.5 when inserting a symbol | Excel Discussion (Misc queries) | |||
Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
Weighed average if the adjecent column contains blanks | Excel Discussion (Misc queries) | |||
linking files with blanks | Excel Discussion (Misc queries) | |||
Filling in blanks. | Excel Worksheet Functions |