+ Reply to Thread
Results 1 to 10 of 10

Consecutively numbering cells

  1. #1
    Registered User
    Join Date
    09-29-2014
    Location
    Midwest USA
    MS-Off Ver
    365
    Posts
    4

    Consecutively numbering cells

    I want to start a numbering system (say, 51249 in A1), then consecutively numbering the cells going down vertical until I reach the cell that would be at the bottom of an 8.5x11 sheet of paper. Then start at B1 with the next consecutive number after the last cell in column A. I'm hoping there is a formula for this! Any help will be appreciated.

  2. #2
    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
    44,091

    Re: Consecutively numbering cells

    Put 51249 in A1. Put 51250 in A2. Select cells A1 and A2. Hover the mouse over the bottom right hand corner, until the mouse pointer turns into a cross hair. Left click and drag down with the mouse until you have filled enough cells. You could also populate B1 and B2, C1 and C2, etc., and select all the cells and drag down.

    Regards, TMS
    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


  3. #3
    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
    44,091

    Re: Consecutively numbering cells

    Sorry, I have been prompted that I might have not answered the question, or answered the wrong question.

    Instead of hard coding numbers, you can just put the first number in cell A1 and, in A2, put the formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and drag down.

    Now, here lies the problem: there is no way of knowing how many rows you will need to fill 8 (or 11) inches of paper. This will depend on the printer, if the printer can print without borders, what font and font size you have chosen.

    So, you'll need ti decide what the parameters are and drag down as far as you think is necessary and do a Print Preview. If it's too short, continue dragging down. If it's too long, select the excess cells and drag them to the top of the next column. Continue dragging down. Once you know how long the page needs to be, just keep continuing copying and dragging the formula.

    @Ford: better?

  4. #4
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Consecutively numbering cells

    Quote Originally Posted by TMS View Post
    .............. Once you know how long the page needs to be, just keep continuing copying and dragging the formula.
    Once you have column A completed then take the number of rows in A (for the purposes of this we'll say there were 30) and in B1 put the formula
    Please Login or Register  to view this content.
    , drag that down then drag the range B1-B30 across
    If someone has helped you then please add to their Reputation

  5. #5
    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
    44,091

    Re: Consecutively numbering cells

    See the attached example, 50 rows deep.

    Regards, TMS
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Consecutively numbering cells

    I think I have the basis of a Sub to do this however the dimensions dont seem right.

    From what I can find out, A4 is 11.69 inches in height so the number of rows should be calculable by taking the ((height - any margins) * dpi) to give the number of pixels printable, we should then simply be able to add the height of each row until we reach that value......

    my understanding is that the Excel row height is in pixels and mine is currently set to 15 so that would mean that at 300dpi Id get 233 rows which clearly doesnt sound right?

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Consecutively numbering cells

    hmm if you use Page Break Preview, you could get a close approximation of how far down to go?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  8. #8
    Registered User
    Join Date
    09-29-2014
    Location
    Midwest USA
    MS-Off Ver
    365
    Posts
    4

    Re: Consecutively numbering cells

    I used your method and that worked perfectly. I do have print preview, so I was able to know how far down to scroll. Thanks All!

    Quote Originally Posted by TMS View Post
    Sorry, I have been prompted that I might have not answered the question, or answered the wrong question.

    Instead of hard coding numbers, you can just put the first number in cell A1 and, in A2, put the formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and drag down.

    Now, here lies the problem: there is no way of knowing how many rows you will need to fill 8 (or 11) inches of paper. This will depend on the printer, if the printer can print without borders, what font and font size you have chosen.

    So, you'll need ti decide what the parameters are and drag down as far as you think is necessary and do a Print Preview. If it's too short, continue dragging down. If it's too long, select the excess cells and drag them to the top of the next column. Continue dragging down. Once you know how long the page needs to be, just keep continuing copying and dragging the formula.

    @Ford: better?

  9. #9
    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
    44,091

    Re: Consecutively numbering cells

    You're welcome.

  10. #10
    Registered User
    Join Date
    04-04-2015
    Location
    -
    MS-Off Ver
    -
    Posts
    5

    Re: Consecutively numbering cells

    Thanks for your help!

+ 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. Name cells consecutively in first row as a loop?
    By perola.rike in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 05-23-2014, 03:46 PM
  2. [SOLVED] Need formula to sum range of cells consecutively across a row not duplicating used cells
    By angie18a in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-28-2013, 03:46 PM
  3. Replies: 14
    Last Post: 05-10-2013, 03:27 PM
  4. Formula for counting cells consecutively
    By d1scopants in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-28-2012, 09:11 AM
  5. Macro for grouping & consecutively numbering groups
    By clarisoon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-05-2008, 04:19 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1