I have about 5000 rows of data
I must insert nine blank rows between each two rows
this is the easy part till now....
then i want to fill these nine blank rows with series (linear-trend)

the problem that I want to fill each nine blank rows
seprately...because when i fill all the blank rows... they change the
values in the original rows !!!

im trying to interpolate values (the nine blank rows) between the
two original rows ....mainly i have X & Y values along with other
values but i need interpolation only for the X & Y .

this is the code i used to insert the blank 9 rows...i wish any one can complete it concerning teh filling values issue

Code:

Option Explicit
Sub InsertBlankRows()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
Dim Rng As Range
Dim lastrw As Long
numRows = InputBox("How many Rows")
lastrw = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range(Cells(1, "A"), Cells(lastrw, "A"))
For r = Rng.Rows.Count To 1 Step -1
Rng.Rows(r + 1).Resize(numRows).EntireRow.Insert
Next r
Application.ScreenUpdating = True
End Sub

you can look at this screenprint ... i think it explian

You can also google it! Try "inserting blank rows in macros"

L

10-19-2005, 06:41 PM

mstraf

Laurel,
"interpolate" does not mean "insert"!
Say you have two numbers A and B, and you want a series of 10 numbers between the two of them. To compute the new numbers using a linear interpolation, you can use:
X(i) = A + i * (B - A) / 10
so that X(0) = A and X(10) = B
It is used to create "smooth" gradients. As a home work, find the formula for a "quadratic" interpolation :-)

To the original poster, from the jpeg it looks like you inserted the blank rows, and that you fill out the first interpolation. Is you problem in the next series or what?

Marco (I am a physicist, for whoever who was wondering)

10-19-2005, 07:57 PM

geophysicist

well i managed to do a macro .... to insert blank rows ...

the main problem that i want to interpolate the whole new blank rows !

i want a macro to do that ..... coz i have huge data

the macro must interpolate every nine blank rows.... coz i tried 2 interpolate the whole file ... it change the original values

i wish i could explianed it well

10-20-2005, 12:02 AM

Laurel

marco,

As homework, BITE ME! :p

in·ter·po·late ( P ) Pronunciation Key (n-tūrp-lt)
v. in·ter·po·lat·ed, in·ter·po·lat·ing, in·ter·po·lates
v. tr.
To insert or introduce between other elements or parts.

To insert (material) into a text.
To insert into a conversation. See Synonyms at introduce.
To change or falsify (a text) by introducing new or incorrect material.
Mathematics. To estimate a value of (a function or series) between two known values.

v. intr.
To make insertions or addition

Did I mention that I'm an eclipseophysicist?

LOVE YA!
L

10-20-2005, 05:46 AM

geophysicist

so either interpolate or insert ... some one found a solution ?

it 'll save my time .... about 3 days of work ...

i wish some one help me here :)

10-21-2005, 11:54 AM

peaches

Solution

I have tested this with some sample data and it works. I haven't tried to optimize it; I left as much detail in the code so you could see how it works.

Sub Interpolate()

'Option Explicit

Dim DataIn() As Variant
Dim DataOut() As Variant

Dim i As Long
Dim j As Long
Dim k As Long
Dim maxData As Long

Dim delta3 As Single
Dim delta4 As Single

Dim WSName As Worksheet

ReDim DataIn(6000, 4)

'define the active sheet as the input worksheet

Set WSName = ActiveWorkbook.Worksheets("DataIn")

'load worksheet range into input array - this is very fast

DataIn = WSName.Range("a1:d10")

'get the size of the input array

maxData = UBound(DataIn, 1)

'define the size of the output array

ReDim DataOut(1 To 10 * maxData + 1, 1 To 4)

'loop thru the input array

For i = 1 To maxData - 1

'define the subscript for the ourput array

j = 10 * (i - 1) + 1

'copy columns 1 & 2 from the input array to the output array

I posted my problem in another forum ...& some one helped me

Code:

Option Explicit
Sub InsertBlankRows()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
Dim Rng As Range
Dim lastrw As Long
Dim Ar As Range
Dim StepValue1
Dim StepValue2
Dim Ar1 As Range
Dim AR2 As Range
numRows = InputBox("How many Rows")
lastrw = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range(Cells(1, "A"), Cells(lastrw, "A"))
For r = Rng.Rows.Count To 1 Step -1
Rng.Rows(r + 1).Resize(numRows).EntireRow.Insert
Next r
Set Rng = Columns(1).SpecialCells(xlBlanks)
For Each Ar In Rng.Areas
Set Ar1 = Ar.Offset(-1, 0).Resize(Ar.Rows.Count + 1)
Set AR2 = Ar1.Resize(Ar1.Rows.Count + 1)
StepValue1 = (AR2(AR2.Count).Offset(0, 2) - _
Ar1(1).Offset(0, 2)) / Ar1.Count
StepValue2 = (AR2(AR2.Count).Offset(0, 3) - _
Ar1(1).Offset(0, 3)) / Ar1.Count
Ar1.Offset(0, 2).DataSeries Rowcol:=xlColumns, _
Type:=xlLinear, Date:=xlDay, _
Step:=StepValue1, Trend:=False
Ar1.Offset(0, 3).DataSeries Rowcol:=xlColumns, _
Type:=xlLinear, Date:=xlDay, _
Step:=StepValue2, Trend:=False
Next

End Sub

i couldn't identify what is wrong with your code... but i really apperciate your help
thanks