+ Reply to Thread
Results 1 to 8 of 8

Insert row macro

  1. 05-19-2011, 03:14 PM #1
    dubele
    dubele is offline
    Registered User
    Join Date
    05-19-2011
    Location
    Harleysville, Pa
    MS-Off Ver
    Excel 2003
    Posts
    4

    Insert row macro

    Hello,

    I am trying to create a macro that will insert a row when a new value in a column is encountered in a row. I got close, but I think I am missing the down code....what is the code for the down button?

    I'd like to take this:

    700006112
    700006112
    700007653
    700007653
    700007653
    700008424
    700008424
    700008424
    700008852
    700008852
    700008852
    700008852

    and wind up with this:

    700006112
    700006112

    700007653
    700007653
    700007653

    700008424
    700008424
    700008424

    700008852
    700008852
    700008852
    700008852

    Thanks,
    Doug
    Reply With QuoteRegister To Reply
  2. 05-19-2011, 05:47 PM #2
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,440

    Re: simple insert row macro

    Try this:

    Please Login or Register  to view this content.

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 05-19-2011, 06:44 PM #3
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: simple insert row macro

    here's a formula /sort manual approach
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  4. 05-20-2011, 09:58 AM #4
    dubele
    dubele is offline
    Registered User
    Join Date
    05-19-2011
    Location
    Harleysville, Pa
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: simple insert row macro

    Thanks TMShucks! This worked. The values are in column B, so I changed the "A"s to "B"s.

    Could you do me one more favor and explain the code to me?

    Thanks a million!

    Doug
    Reply With QuoteRegister To Reply
  5. 05-20-2011, 02:34 PM #5
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,440

    Re: simple insert row macro

    OK ...

    The first statement, Option Explicit, ensures that you define all your variables. It helps to avoid mis-spelling.

    Please Login or Register  to view this content.

    Set up some variables

    Please Login or Register  to view this content.

    Switch off screen updating so that Excel doesn't have to refresh the screen; this makes the code run a lot faster

    Please Login or Register  to view this content.

    Determine the last row of data; this uses column A ... as it's the only one populated in the example. Generally, you need to pick a column you know has data in every row.

    Please Login or Register  to view this content.

    Start looping through the rows, from the bottom up; in this case, we're actually starting the loop one row from the bottom because of the comparison.

    Please Login or Register  to view this content.

    Compare the values in the two rows and, if they're different, insert a row

    Please Login or Register  to view this content.

    Loop ...

    Please Login or Register  to view this content.

    Refresh the screen and finish

    Please Login or Register  to view this content.


    Regards
    Reply With QuoteRegister To Reply
  6. 05-20-2011, 05:17 PM #6
    dubele
    dubele is offline
    Registered User
    Join Date
    05-19-2011
    Location
    Harleysville, Pa
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: simple insert row macro

    Thanks again for your help TMShucks! This is a big help!

    Doug
    Reply With QuoteRegister To Reply
  7. 05-20-2011, 05:22 PM #7
    dubele
    dubele is offline
    Registered User
    Join Date
    05-19-2011
    Location
    Harleysville, Pa
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: simple insert row macro

    martindwilson,

    Very clever! I like this one also....why didn't I think of this??
    Reply With QuoteRegister To Reply
  8. 05-20-2011, 05:52 PM #8
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,440

    Re: Insert row macro

    You're welcome.

    If this answers your question, please mark the thread as Solved. See my signature for details or the FAQ.

    Regards
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1