Results 1 to 7 of 7
  1. #1
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Quicken the use of fill handle - Excel 2010

    I may use the fill handle to add consecutive numbers to a field, e.g., A1=1, A2=2, A3=3, etc. This works great until I have thousands of records. I suppose that I could enter a forrmula to accomplish the same thing, but is there a way to quickly have a large number of cells filled with my numbering pattern? Thanks.
    JimmyW
    Helena, MT

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    I am sure there is a quick way, maybe with paste special, but here is a very versatile solution. Enter the following formula in s standard module then assign the macro s a shortcut key such as Ctrl-z (see image 2). To use the macro, highlight the cells you want to increment under the starting value the hit Ctrl-z. The cells will be incremented down. You can change the incrementing value to an integer, a decimal, percentage, formula, etc.. I have this sitting in my personal workbook and I use it all the time.

    HTH,
    Maud

    before:
    increment1.png

    After Ctrl-z:
    increment2.png


    Code:
    Public Sub Increment()
    Dim Cell As Range
    Dim num As Double, inc As Double
    inc = 1 'CHANGE INCREMENTING VALUE
    num = ActiveCell.Offset(-1, 0) + inc
    For Each Cell In Selection
        Cell = num
        num = num + inc
    Next Cell
    End Sub

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Jimmy,

    Here's a way to do what you want without a macro.
    1. Hit the F5 function key. In the reference box, put A100 or A1000, whatever you need. Click OK. You're now in the cell you selected.

    2. While in that cell, hit CTRL+SHIFT+HOME while holding all 3 keys together. That will select all cells from where you are up to A1.

    3. Enter anything you want (this is not important as you'll see in a moment). Instead of using Enter to confirm, use SHIFT+CTRL+ENTER (again, holding all 3 keys). This will put whatever you entered in all these cells.

    4. go to B1, enter 1 and in B2 enter 2.

    5. select B1 and B2. Goto the fill handle for these and double click. This will fill all the cells consecutively while there are entries to the next left column. (hence the first 3 steps)

    6. delete col A.

    Not as elegant or versatile as Maud's macro but it works.

    Fred

  4. #4
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can also:
    Type 1 in the first cell, then in the name box (left of the formula bar), type the range you want (eg: A1:A1000) and press Enter, then use the Fill - Series button on the Home tab.
    Or of course if you already have a block of data in the sheet you are trying to fill alongside, you can just double-click the fill handle.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Where do you get all these little tricks? While I don't consider myself any kind of expert in Excel while I've been using it for some 20+ years, all these little tricks continue to amaze me. Like typing a range in the Name box ! I'm sure once I've selected cells using that little trick, I can do more than just fill it.

    Fred

  7. #7
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts
    A belated thanks to everyone! I went with Rory's suggestion, which worked great.
    JimmyW
    Helena, MT

Posting Permissions

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