DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 10 of 10

Hybrid View

  1. #1
    Join Date
    Oct 2005

    Exclamation Need a Macro plz !

    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

    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


    Last edited by geophysicist; 10-19-2005 at 04:48 PM. Reason: forgot something !

  2. #2
    Join Date
    Jun 2004
    Houston area
    What are you trying to DO with those values?
    A balanced diet is a cookie in each hand.

  3. #3
    Join Date
    Oct 2005
    i have many X & Y values .... between each two values i want to insert NINE VALUES ( i.e. interpolate nine values between each two original values)

    check the screen shot

  4. #4
    Join Date
    Jun 2004
    Houston area
    Ok, you might be a geophysicist, but ya dun't hafta use "BIG" words like interpolate!

    Can't ya just use words we'll understand, like "insert"? :-Ž

    I don't program in Excel so I'll hafta think about this on my way home. While I'm driving home, look at this and see if it helps:


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

    Last edited by Laurel; 10-19-2005 at 06:24 PM.
    A balanced diet is a cookie in each hand.

  5. #5
    Join Date
    Nov 2003
    Alameda, CA
    "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)
    "There are two ways to write error-free programs. Only the third one works."

  6. #6
    Join Date
    Oct 2005
    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

  7. #7
    Join Date
    Jun 2004
    Houston area

    As homework, BITE ME!

    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!
    A balanced diet is a cookie in each hand.

  8. #8
    Join Date
    Oct 2005
    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

  9. #9
    Join Date
    Oct 2005

    Exclamation 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

    DataOut(j, 1) = DataIn(i, 1)
    DataOut(j, 2) = DataIn(i, 2)

    'get the interpolation step size for columns 3 and 4

    delta3 = (DataIn(i + 1, 3) - DataIn(i, 3)) / 10#
    delta4 = (DataIn(i + 1, 4) - DataIn(i, 4)) / 10#

    Debug.Print i; j; delta3; delta4

    'loop thru the interpolation steps

    For k = 1 To 10

    'define the interpolated values for columns 3 & 4
    'and store in the output array

    DataOut(j + (k - 1), 3) = DataIn(i, 3) + (k - 1) * delta3
    DataOut(j + (k - 1), 4) = DataIn(i, 4) + (k - 1) * delta4

    Next '[k] end of interpolation loop

    Next '[i] end of input values

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

    i = maxData
    j = 10 * (i - 1) + 1

    DataOut(j, 1) = DataIn(i, 1)
    DataOut(j, 2) = DataIn(i, 2)
    DataOut(j, 3) = DataIn(i, 3)
    DataOut(j, 4) = DataIn(i, 4)

    'define the active sheet as the output worksheet

    Set WSName = ActiveWorkbook.Worksheets("DataOut")

    'load output array to worksheet range - this is very fast

    WSName.Range("A1").Resize(10 * maxData + 1, 4).Value = DataOut

    End Sub

Similar Threads

  1. word macro problem : No reply
    By namella in forum Mobile
    Replies: 0
    Last Post: 08-25-2005, 07:27 AM
  2. plz plz plz help me(java)
    By kamon in forum Java
    Replies: 4
    Last Post: 08-10-2005, 10:30 AM
  3. ANN: VB.IDE Macro Studio
    By Leigh in forum vb.announcements
    Replies: 0
    Last Post: 09-10-2001, 07:12 AM
  4. Run Access Macro
    By Bob in forum VB Classic
    Replies: 2
    Last Post: 07-30-2001, 12:48 PM
  5. VB.IDE Macro Studio version 1.1 now available.
    By Leigh Pointer in forum vb.announcements
    Replies: 0
    Last Post: 03-13-2000, 04:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center