This is something I've run across again and again that I hope to now get a
solution for. Most of the time, I use =ROW-1, or a variation thereof, to
get column A to show numbers 1 to whatever, depending on the # of rows in a
print area.

However, every once in a while numbers _must_ still display sequentially,
but must be "real" numbers. So instead of =ROW-1 showing in a cell, one
would see an actual #, 1, 2, 3, 4 ... etc. I've always had to type these in
manually. Today I had to type in up to row 100!

My question is if it's possible to programmatically dump the numbers in in a
selected number of cells downwards in column A?? i.e., if I were to select
from A2 to A51, for example, could the macro start filling in the cells
numerically - A2 would then have a 1 in it, A3 would have 2, A4 would have
3, etc., etc.?

This would be a god-send, if it were possible to do.

I just got to thinking, what if the case were of running out of rows in a
spreadsheet? Let's say a sheet that reached row #A51 which had number 50 in
it. I'd have to add rows after that point. If I added 50 more rows and
then selected A52 to A101, would there be a way to have the macro then
populate the cells from 51 to 100? To do both, starting from 1 like the
above case, or starting from 51 like this second case, I'm guessing it would
be a matter of prompting the user for the starting number to begin the
numbering (?).

Just a thought. I don't know if this can be done, but it would save so much
grief and time and would save some poor fingers! <g

Thanks!

One method of populating a column with numbers is to enter 1 in top cell.

Right-click on fill handle of the this cell and drag down 100 rows.

Release the button and select "Fill Series" from the menu.

Another method is to enter 1 in the top cell, select the namebox and type in
A1:A100 and <ENTER (A1:A100 to be adjusted to your range and column)

Now, EditFillSeriesLinearOK

If you need numbers later past 100, just select 99 and 100 and drag/copy down.

Gord Dibben Excel MVP

On Tue, 13 Jul 2004 16:43:30 -0400, "StargateFanFromWork"
wrote:

This is something I've run across again and again that I hope to now get a
solution for. Most of the time, I use =ROW-1, or a variation thereof, to
get column A to show numbers 1 to whatever, depending on the # of rows in a
print area.

However, every once in a while numbers _must_ still display sequentially,
but must be "real" numbers. So instead of =ROW-1 showing in a cell, one
would see an actual #, 1, 2, 3, 4 ... etc. I've always had to type these in
manually. Today I had to type in up to row 100!

My question is if it's possible to programmatically dump the numbers in in a
selected number of cells downwards in column A?? i.e., if I were to select
from A2 to A51, for example, could the macro start filling in the cells
numerically - A2 would then have a 1 in it, A3 would have 2, A4 would have
3, etc., etc.?

This would be a god-send, if it were possible to do.

I just got to thinking, what if the case were of running out of rows in a
spreadsheet? Let's say a sheet that reached row #A51 which had number 50 in
it. I'd have to add rows after that point. If I added 50 more rows and
then selected A52 to A101, would there be a way to have the macro then
populate the cells from 51 to 100? To do both, starting from 1 like the
above case, or starting from 51 like this second case, I'm guessing it would
be a matter of prompting the user for the starting number to begin the
numbering (?).

Just a thought. I don't know if this can be done, but it would save so much
grief and time and would save some poor fingers! <g

Thanks!

Hi,

Assuming your list started from E26 try:

=ROW()-ROW($E$26)+1

and copy it down.

HTH

Peter Beach

"StargateFanFromWork" wrote in message
...
This is something I've run across again and again that I hope to now get a
solution for. Most of the time, I use =ROW-1, or a variation thereof, to
get column A to show numbers 1 to whatever, depending on the # of rows in
a
print area.

However, every once in a while numbers _must_ still display sequentially,
but must be "real" numbers. So instead of =ROW-1 showing in a cell, one
would see an actual #, 1, 2, 3, 4 ... etc. I've always had to type these
in
manually. Today I had to type in up to row 100!

My question is if it's possible to programmatically dump the numbers in in
a
selected number of cells downwards in column A?? i.e., if I were to
select
from A2 to A51, for example, could the macro start filling in the cells
numerically - A2 would then have a 1 in it, A3 would have 2, A4 would have
3, etc., etc.?

This would be a god-send, if it were possible to do.

I just got to thinking, what if the case were of running out of rows in a
spreadsheet? Let's say a sheet that reached row #A51 which had number 50
in
it. I'd have to add rows after that point. If I added 50 more rows and
then selected A52 to A101, would there be a way to have the macro then
populate the cells from 51 to 100? To do both, starting from 1 like the
above case, or starting from 51 like this second case, I'm guessing it
would
be a matter of prompting the user for the starting number to begin the
numbering (?).

Just a thought. I don't know if this can be done, but it would save so
much
grief and time and would save some poor fingers! <g

Thanks!

On Tue, 13 Jul 2004 14:38:10 -0700, Gord Dibben <gorddibbATshawDOTca
wrote:

One method of populating a column with numbers is to enter 1 in top cell.

Right-click on fill handle of the this cell and drag down 100 rows.

Release the button and select "Fill Series" from the menu.

Oh, god, to think of all these years when I've been typing all the
numbers in manually! <lol

(Told yas I was a newbie even if a power user! D'uh! <g)

Unbelievably easy! This worked just fine. The method below would be
best if I knew ahead of time what the beginning and end cells would
be. I see a use for both methods.

Thank you so much for this! <g

Another method is to enter 1 in the top cell, select the namebox and type in
A1:A100 and <ENTER (A1:A100 to be adjusted to your range and column)

Now, EditFillSeriesLinearOK

If you need numbers later past 100, just select 99 and 100 and drag/copy down.

Gord Dibben Excel MVP

On Tue, 13 Jul 2004 16:43:30 -0400, "StargateFanFromWork"
wrote:

This is something I've run across again and again that I hope to now get a
solution for. Most of the time, I use =ROW-1, or a variation thereof, to
get column A to show numbers 1 to whatever, depending on the # of rows in a
print area.

However, every once in a while numbers _must_ still display sequentially,
but must be "real" numbers. So instead of =ROW-1 showing in a cell, one
would see an actual #, 1, 2, 3, 4 ... etc. I've always had to type these in
manually. Today I had to type in up to row 100!

My question is if it's possible to programmatically dump the numbers in in a
selected number of cells downwards in column A?? i.e., if I were to select
from A2 to A51, for example, could the macro start filling in the cells
numerically - A2 would then have a 1 in it, A3 would have 2, A4 would have
3, etc., etc.?

This would be a god-send, if it were possible to do.

I just got to thinking, what if the case were of running out of rows in a
spreadsheet? Let's say a sheet that reached row #A51 which had number 50 in
it. I'd have to add rows after that point. If I added 50 more rows and
then selected A52 to A101, would there be a way to have the macro then
populate the cells from 51 to 100? To do both, starting from 1 like the
above case, or starting from 51 like this second case, I'm guessing it would
be a matter of prompting the user for the starting number to begin the
numbering (?).

Just a thought. I don't know if this can be done, but it would save so much
grief and time and would save some poor fingers! <g

Thanks!

On Wed, 14 Jul 2004 10:22:21 +1200, "Peter Beach"
wrote:

Hi,

Assuming your list started from E26 try:

=ROW()-ROW($E$26)+1

I tried this and unless I'm missing something there seems to be no
benefit that I could see over =ROW()-1 or variation thereof. If I
re-sort the info in the second column, the above adjusts the numbering
just like =ROW()-1.

See, most of the time, the sheets I'm making are sorted by date. So
any new record must go in sequence and the numbering in column A needs
to accommodate the new entry. So if one day a particular piece of
correspondence went with record #15, if we find a previous letter and
incorporate it into the index sheet, the above might then belong to
#16. =ROW()-1 works perfectly in this type of situation.

But there are couple of index sheets where the numbers need to be
inflexible. For those binders where data is added and deleted
constantly, it turned out to be best to have inflexible row #'s, i.e.,
numerically present rather than present through a formula. We sort by
the data and the numbers are all out of whack, since unlike =ROW()-1
no re-shifting occurs. But one finds the correspondence with no
problem as the index sheet is sorted by date not row #.

When we didn't do this, every time we deleted an entry all the records
would shift re the row # and that would have meant re-shuffling all
the documents within the binder to correspond to the index tab. Much
easier to do a sort to find an empty record to put the new entry in
and then to re-sort by date. Means that when you physically are
looking through the binder all the documentation is out of
chronological order - but it's the index sheet that keeps everything
organized.

Thanks!

and copy it down.

HTH

Peter Beach

"StargateFanFromWork" wrote in message
...
This is something I've run across again and again that I hope to now get a
solution for. Most of the time, I use =ROW-1, or a variation thereof, to
get column A to show numbers 1 to whatever, depending on the # of rows in
a
print area.

However, every once in a while numbers _must_ still display sequentially,
but must be "real" numbers. So instead of =ROW-1 showing in a cell, one
would see an actual #, 1, 2, 3, 4 ... etc. I've always had to type these
in
manually. Today I had to type in up to row 100!

My question is if it's possible to programmatically dump the numbers in in
a
selected number of cells downwards in column A?? i.e., if I were to
select
from A2 to A51, for example, could the macro start filling in the cells
numerically - A2 would then have a 1 in it, A3 would have 2, A4 would have
3, etc., etc.?

This would be a god-send, if it were possible to do.

I just got to thinking, what if the case were of running out of rows in a
spreadsheet? Let's say a sheet that reached row #A51 which had number 50
in
it. I'd have to add rows after that point. If I added 50 more rows and
then selected A52 to A101, would there be a way to have the macro then
populate the cells from 51 to 100? To do both, starting from 1 like the
above case, or starting from 51 like this second case, I'm guessing it
would
be a matter of prompting the user for the starting number to begin the
numbering (?).

Just a thought. I don't know if this can be done, but it would save so
much
grief and time and would save some poor fingers! <g

Thanks!