+ Reply to Thread
Results 1 to 18 of 18

Insert Row When Data Change

  1. 05-25-2021, 03:22 AM #1
    MiglA06
    MiglA06 is offline
    Registered User
    Join Date
    04-27-2019
    Location
    Los Angeles,CA
    MS-Off Ver
    Office 2010
    Posts
    21

    Insert Row When Data Change

    Hello Everyone,

    I've searched the threads but was unable to get any of the VBA suggested solutions to work for me. I'm looking to insert a row when there is data change in column A. I would like it to go from the picture on the left to the one on the right. The Range for A is "A2:A400". Also, this might be asking too much but is there a way to also fill the row with a color when doing this? If not just the row insert. Thanks


    Insert.png Insert 1.png
    Reply With QuoteRegister To Reply
  2. 05-25-2021, 03:44 AM #2
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,288

    Re: Insert Row When Data Change 

    Please Login or Register  to view this content.
    Last edited by sintek; 05-25-2021 at 03:48 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!
    Reply With QuoteRegister To Reply
  3. 05-25-2021, 04:05 AM #3
    MiglA06
    MiglA06 is offline
    Registered User
    Join Date
    04-27-2019
    Location
    Los Angeles,CA
    MS-Off Ver
    Office 2010
    Posts
    21

    Re: Insert Row When Data Change

    Thanks for your help but it gave me an error 1004. No cells found on the line below.

    Range("A3:A" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeConstants).EntireRow.Insert
    Reply With QuoteRegister To Reply
  4. 05-25-2021, 04:23 AM #4
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,288

    Re: Insert Row When Data Change

    Well then your file setup is not as per your image above...Upload sample file...
    Reply With QuoteRegister To Reply
  5. 05-25-2021, 04:31 AM #5
    MiglA06
    MiglA06 is offline
    Registered User
    Join Date
    04-27-2019
    Location
    Los Angeles,CA
    MS-Off Ver
    Office 2010
    Posts
    21

    Re: Insert Row When Data Change

    Here's the file
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 05-25-2021, 04:37 AM #6
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,288

    Re: Insert Row When Data Change

    What is the reason for the custom 0's in Col A
    Reply With QuoteRegister To Reply
  7. 05-25-2021, 04:42 AM #7
    MiglA06
    MiglA06 is offline
    Registered User
    Join Date
    04-27-2019
    Location
    Los Angeles,CA
    MS-Off Ver
    Office 2010
    Posts
    21

    Re: Insert Row When Data Change

    it's copying the information from another sheet. I formatted the cell because it shows N/A when there is no info. I used 0;-0;;@ custom format
    Reply With QuoteRegister To Reply
  8. 05-25-2021, 04:45 AM #8
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,288

    Re: Insert Row When Data Change 

    Please Login or Register  to view this content.
    PS...No need to format to 0's just replace the 0 above with N/A
    Reply With QuoteRegister To Reply
  9. 05-25-2021, 04:56 AM #9
    MiglA06
    MiglA06 is offline
    Registered User
    Join Date
    04-27-2019
    Location
    Los Angeles,CA
    MS-Off Ver
    Office 2010
    Posts
    21

    Re: Insert Row When Data Change

    Thanks for your help. It works in the test file but not in my original. Thanks though
    Reply With QuoteRegister To Reply
  10. 05-25-2021, 04:58 AM #10
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,288

    Re: Insert Row When Data Change

    Then original file is not same as test file...Any reason why you upload a sample file that DOES NOT represents your ACTUAL FILE?
    Reply With QuoteRegister To Reply
  11. 05-25-2021, 05:02 AM #11
    MiglA06
    MiglA06 is offline
    Registered User
    Join Date
    04-27-2019
    Location
    Los Angeles,CA
    MS-Off Ver
    Office 2010
    Posts
    21

    Re: Insert Row When Data Change

    No not really. I figured the info was the same. Here's the actual file
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 05-25-2021, 05:43 AM #12
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,288

    Re: Insert Row When Data Change

    There is just so much wrong with the way you are processing your data...
    I would get rid of all those formula's and make use of VBA to manipulate the data...

    This will work based on your data structure...
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  13. 05-25-2021, 10:11 AM #13
    KalilMe
    KalilMe is offline
    Forum Contributor
    Join Date
    04-11-2021
    Location
    Tripoli
    MS-Off Ver
    Excel 2016 ,windows 10 64bit
    Posts
    345

    Re: Insert Row When Data Change

    @sintek it's impressive how can I add line code to summing the values only in COL C after insert row
    Reply With QuoteRegister To Reply
  14. 05-25-2021, 10:43 AM #14
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,288

    Re: Insert Row When Data Change

    @ KalilMe

    Thank you for rep +...I suggest you open your own thread...
    See Forum rules No 4
    Reply With QuoteRegister To Reply
  15. 05-25-2021, 10:44 AM #15
    KalilMe
    KalilMe is offline
    Forum Contributor
    Join Date
    04-11-2021
    Location
    Tripoli
    MS-Off Ver
    Excel 2016 ,windows 10 64bit
    Posts
    345

    Re: Insert Row When Data Change

    @sintek thanks
    Reply With QuoteRegister To Reply
  16. 05-25-2021, 11:19 AM #16
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,747

    Re: Insert Row When Data Change

    Quote Originally Posted by KalilMe View Post
    @sintek it's impressive how can I add line code to summing the values only in COL C after insert row
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

    If you do start a new thread, make sure you include a link to your post on another forum.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]
    Reply With QuoteRegister To Reply
  17. 05-27-2021, 07:58 PM #17
    MiglA06
    MiglA06 is offline
    Registered User
    Join Date
    04-27-2019
    Location
    Los Angeles,CA
    MS-Off Ver
    Office 2010
    Posts
    21

    Re: Insert Row When Data Change

    Thanks that worked.
    Reply With QuoteRegister To Reply
  18. 05-28-2021, 03:39 AM #18
    sintek
    sintek is offline
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,288

    Re: Insert Row When Data Change

    Glad I could help...tx for rep +
    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)

Similar Threads

  1. Insert lines at change of data and add sub totals to copy to new tab (do through a loop?)
    By theglitch in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 10-01-2019, 05:07 PM
  2. [SOLVED] Macro to insert blank line after change of data keeps missing one entry?
    By Graham Pall in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-31-2015, 03:34 AM
  3. [SOLVED] Insert rows after change in data with certain data in each cell
    By jscalem in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-15-2014, 07:13 AM
  4. [SOLVED] Insert Row at each data change
    By hnoshea in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-23-2013, 02:02 PM
  5. [SOLVED] I want xl to insert a fixed date in a column to highlight a change made in a row of data
    By xl5 in forum Excel General
    Replies: 2
    Last Post: 11-27-2012, 05:55 AM
  6. Insert Two Rows after change in column data
    By RRick in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-04-2011, 02:32 PM
  7. Copy paste data, insert rows, insert data and change . to -
    By xWiZardx in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-16-2010, 01:44 AM

Tags for this Thread

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