Need a Macro plz !

[wizardgeophysicist]

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 !!!

so plz i want a macro to fill nine blanks rows then the next nine
blanks rows ! and so on ..

 

[Tom Ogilvy]

After you have inserted you 9 blank rows, then

Dim rng as Range, ar as Range
set rng = columns(1).SpecialCells(xlblanks)
for each ar in rng.Areas
' now ar should hold a reference to a set of 9 blank cells in column A
' you can use that to do your fill. It will then loop to the next set
of
' 9 blank cells
Next ar

 

[wizardgeophysicist]

sorry i can't get your point ...
here is the macro i use ,,,, plz clarify


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


thanks for your help

 

[Tom Ogilvy]

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
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
' now ar should hold a reference to a set of 9 blank cells in column A
' you can use that to do your fill. It will then loop to the next set
of
' 9 blank cells
Next ar
Application.ScreenUpdating = True
End Sub

Not much more I can tell you. Just saying serialize doesn't explain what
you are trying to do.

 

[wizardgeophysicist]

well 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 .

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

http://img436.imageshack.us/img436/1491/excel1vq.jpg

http://img436.imageshack.us/my.php?image=excel1vq.jpg

 

[Tom Ogilvy]

Didn't you already post about this and get a formula solution which you were
happy with. Use that. Just incorporate it in your code.

 

[WizardGeophysicist]

Well im sorry for bothering .... but the main problem that i can't
insert your solution into my code ,.... so i re-defined my problem
again !...

so i'll be glad if u sent the whole code to me ... after you see the
screen shot !

i really want this macro .... it 'll save about 2 days of work

 

[WizardGeophysicist]

No answer yet ?!!

 

[Tom Ogilvy]

I did supply a solution then, but you ignored it and expressed your
exuberant joy with a different solution offer by someone else. It certainly
would be silly for me to then offer up my apparently inferior solution
again. Perhaps you should contact that person.

 

[WizardGeophysicist]

Well i think u misunderstood me !!
i didn't get your solution so i re-phrased my problem again & send u a
screen shot !

& no one offered me solution except u ... i think u should re-check the
posts again !

i asked for the complete code wiz ur soultion because i couldn't make
it myself i wish the image got clearer now for you.

 

[Tom Ogilvy]

You should be able to get the information you need here:
http://tinyurl.com/8styv

 

[WizardGeophysicist]

thanks for your help..
now i use your code & set the range for 2 columns instead of one column
& make a loop ... right ?

i'll be gratefull if u made this part or showed me how

 

[Tom Ogilvy]

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

 

[WizardGeophysicist]

Thank you very much for your help and support.

You really saved me