+ Reply to Thread
Results 1 to 3 of 3

Insert Row When Column Amount Changes

  1. #1
    Ronald \Tony\ Johnson
    Guest

    Insert Row When Column Amount Changes

    Here is an example of what I am talking about.

    A B C D
    1 A B C D
    2 2 41100 274 584
    3 2 41100 274 625
    4 2 41100 274 333
    5 2 41100 274 102
    6 2 41100 274 957
    7 2 41100 274 362
    8 2 41100 274 651
    9 2 41100 274 611
    10 2 41100 284 965
    11 2 41100 284 336
    12 2 41100 284 251
    13 2 41100 284 362
    14 2 41100 284 859
    15 2 41100 284 512
    16 2 41100 284 669
    17 2 41100 284 512


    I need to insert a new row every time the amount in Column "C"
    changes to a different amount than the previous one.
    There are approx 16,000 rows in this file or I would not be asking..lol

    Thanks for any help you can offer.


    --
    Tony Johnson

    --
    Tony Johnson

  2. #2
    galimi
    Guest

    RE: Insert Row When Column Amount Changes

    Tony,

    You can have a row insert automatically with the following code embedded as
    a change event for the sheet in question.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False


    If Target.Column = 3 Then
    Target.Rows.Insert
    End If
    Application.EnableEvents = True

    End Sub

    A better way to do this would be to have an add-in that activates a
    withevents class intercept of all Worksheet Change events.
    --
    http://HelpExcel.com
    1-888-INGENIO
    1-888-464-3646
    x0197758


    "Ronald "Tony" Johnson" wrote:

    > Here is an example of what I am talking about.
    >
    > A B C D
    > 1 A B C D
    > 2 2 41100 274 584
    > 3 2 41100 274 625
    > 4 2 41100 274 333
    > 5 2 41100 274 102
    > 6 2 41100 274 957
    > 7 2 41100 274 362
    > 8 2 41100 274 651
    > 9 2 41100 274 611
    > 10 2 41100 284 965
    > 11 2 41100 284 336
    > 12 2 41100 284 251
    > 13 2 41100 284 362
    > 14 2 41100 284 859
    > 15 2 41100 284 512
    > 16 2 41100 284 669
    > 17 2 41100 284 512
    >
    >
    > I need to insert a new row every time the amount in Column "C"
    > changes to a different amount than the previous one.
    > There are approx 16,000 rows in this file or I would not be asking..lol
    >
    > Thanks for any help you can offer.
    >
    >
    > --
    > Tony Johnson
    >
    > --
    > Tony Johnson


  3. #3
    Jim May
    Guest

    Re: Insert Row When Column Amount Changes

    The below code works in an example I have - based on a change in Column A (in my example).
    Adapt to your needs (Chg A to C)..

    Sub InsertRow_A_Chg()
    Dim Lrow As Long, vcurrent As String, i As Long
    '// find last used cell in Column A
    Lrow = Cells(Rows.Count, "A").End(xlUp).Row
    '// get the value of that cell in Column A (column 1)
    vcurrent = Cells(Lrow, 1).Value
    '// rows are inserted by looping from bottom up
    For i = Lrow To 2 Step -1
    If Cells(i, 1).Value <> vcurrent Then
    vcurrent = Cells(i, 1).Value
    Rows(i + 1).Resize(2).Insert 'Rows(i + 1).Insert to only Insert One Blank Row
    End If
    Next i
    End Sub

    Hope this helps,
    Jim May
    "Ronald "Tony" Johnson" <tonyj1965@email.wintu.edu> wrote in message news:ubyFDtEUFHA.2892@TK2MSFTNGP14.phx.gbl...
    Here is an example of what I am talking about.

    A B C D
    1 A B C D
    2 2 41100 274 584
    3 2 41100 274 625
    4 2 41100 274 333
    5 2 41100 274 102
    6 2 41100 274 957
    7 2 41100 274 362
    8 2 41100 274 651
    9 2 41100 274 611
    10 2 41100 284 965
    11 2 41100 284 336
    12 2 41100 284 251
    13 2 41100 284 362
    14 2 41100 284 859
    15 2 41100 284 512
    16 2 41100 284 669
    17 2 41100 284 512


    I need to insert a new row every time the amount in Column "C"
    changes to a different amount than the previous one.
    There are approx 16,000 rows in this file or I would not be asking..lol

    Thanks for any help you can offer.


    --
    Tony Johnson

    --
    Tony Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1