Numbering a range of cells in a column without fill handle?

[shedboyxx]

Hi folks. First a quick thanks ahead of time as this has been a very generous and productive forum for me.

I'm wondering if there is a way to number a range of cells without using the fill handle.

Yesterday I had to number +31K in a spreadsheet and I just about started napping while holding the mouse down to advance numbers with the fill handle.

Is there another method to do this with a command? I know there is probably a macro but I'm more looking for something I can use from workbook to workbook. It would be a time saver if I discovered this.

TIA

Jim

 

[tigeravatar]

shedboyxx,

If you're numbering from A1:A31000, you can do the following:

1. Press F5 to bring up the GoTo dialog box
2. Type in A31000
3. Press Enter
4. You should now be at cell A31000
5. In cell A31000, type in the following formula:
   • =Row()
6. Copy cell A31000
7. Press Ctrl+Shift+Up
8. Paste

Hope that helps,
~tigeravatar

 

[Analyze_This]

Not sure why you'd want to do this, but there is a workaround that doesn't involve a macro. First, in A1, put something random (letter, number, doesn't matter). Select A1 and copy.

Now here's the trick: with A1 the active cell, go to the last row in your range and Select+click. This will highlight A1 all the way through Awhatever. Now Ctrl+V to paste. This will fill in all the cells with your random character.

Now go to B1 and put in 1, and then B2 and put 2. Highlight B1:B2 and double click the fill handle. If you did this correctly, the fill handle will continue counting until it reaches the end of the range in column A.

Need to number columns? follow the same steps then copy and paste special and choose "Transpose" to paste them horizontally instead of vertically.

You're welcome

 

[shedboyxx]

Thank you both!

I tried tigeravatar's method wuith great success. Will try Analyze_This' as well.

Cool!

 

[Rick Rothstein]

One more method...

Put 1 in the cell that you want to start the numbering at and then, with that cell selected...

XL2003: Click Edit/Fill/Series on the menu bar

XL2007: Select the (Home tab)(Editing panel), click the downward pointing arrow icon and select Series from the popup menu.

Select the Columns option button, Linear Type, Step Value of 1, Stop Value of 31000 and click OK.