+ Reply to Thread
Results 1 to 9 of 9

code to auto number rows - that will update when rows are added/deleted

  1. #1
    Registered User
    Join Date
    08-12-2015
    Location
    Milwaukee, WI
    MS-Off Ver
    2010
    Posts
    8

    code to auto number rows - that will update when rows are added/deleted

    I need code to add row numbers in a chart. Like, 1, 2, 3, etc. I've used
    Please Login or Register  to view this content.
    and that auto numbers the rows; however, it will not update the numbers if one row is deleted in the middle of the section. I've actually tried lots of things, but haven't come up with something that works when a row is deleted. Thanks.

  2. #2
    Registered User
    Join Date
    08-05-2015
    Location
    Finland
    MS-Off Ver
    Home and Business 2013
    Posts
    50

    Re: code to auto number rows - that will update when rows are added/deleted

    Hello, put number 1 in A1 and in A2 formula : ="OFFSET(A2;-1+1" and pull it down. no spaces OFFSET(A2;-1 ; )+1

  3. #3
    Registered User
    Join Date
    08-12-2015
    Location
    Milwaukee, WI
    MS-Off Ver
    2010
    Posts
    8

    Re: code to auto number rows - that will update when rows are added/deleted

    Thank you for responding but please look at the code you gave me. I cannot decipher what you mean.
    Please Login or Register  to view this content.
    doesn't work.

  4. #4
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: code to auto number rows - that will update when rows are added/deleted

    Quote Originally Posted by dkitzerow View Post
    however, it will not update the numbers if one row is deleted in the middle of the section.
    Yes, it would. If it's not happening for you, please upload an example.

    Also - you can just use this:

    =ROW()

    That will return the row number of the cell you typed the formula into (if that's what number you need). Also - you should definitely not resort to using OFFSET() or any volatile function for something as simple as this.
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

  5. #5
    Registered User
    Join Date
    08-12-2015
    Location
    Milwaukee, WI
    MS-Off Ver
    2010
    Posts
    8

    Re: code to auto number rows - that will update when rows are added/deleted

    I've uploaded. If you delete the row numbered 2, for example, the "new" row 2 remains numbered 3. Thank you.
    Attached Files Attached Files

  6. #6
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: code to auto number rows - that will update when rows are added/deleted

    Ok, now I see what you mean.

    Try this: =ROW()-5

    Where '5' is just an offset to get the numbering to start at 1.


    OR: =COUNTA(E5:E$5)

    SEE ATTACHED:
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-12-2015
    Location
    Milwaukee, WI
    MS-Off Ver
    2010
    Posts
    8

    Re: code to auto number rows - that will update when rows are added/deleted

    Many thanks! But why does my numbering start at 0 unless I add "+1?"
    Attached Files Attached Files

  8. #8
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: code to auto number rows - that will update when rows are added/deleted

    Because the cell, B13 is blank.

    COUNTA() counts the non-blank cells for a given range. For my example in the E column, I started counting on the cell with the value "OR THIS:"

    Make sense?

  9. #9
    Registered User
    Join Date
    08-12-2015
    Location
    Milwaukee, WI
    MS-Off Ver
    2010
    Posts
    8

    Re: code to auto number rows - that will update when rows are added/deleted

    Yes! Thank you for your time and explanation! I'm not sure how to mark this post "resolved."

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. update target sheet with rows as they are added or deleted to master sheet
    By RLR31064 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-20-2014, 05:41 PM
  2. Replies: 1
    Last Post: 01-09-2011, 11:08 PM
  3. Sum values in cell from variable rows (rows are added and deleted)
    By drewship in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-06-2010, 08:38 AM
  4. Replies: 6
    Last Post: 03-27-2008, 06:47 PM
  5. [SOLVED] compare and find added or deleted rows
    By helpwithXL in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-10-2005, 02:55 PM
  6. compare sheets for added or deleted rows
    By helpwithXL in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-10-2005, 01:06 PM
  7. how to detect added or deleted rows in compare
    By helpwithXL in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-29-2005, 09:06 AM

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