Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

fill series and select a range

  Asked By: Reba    Date: Sep 04    Category: MS Office    Views: 1421
  

I wonder if there is an esay way in Excel to do these things:

1. Fill a column with 1 to 1000
2. Select a range of 1000 rows by 1000 columns.

Share: 

 

12 Answers Found

 
Answer #1    Answered By: Sumitra 2004     Answered On: Sep 04

not sure been a while but if i'm not mistaken you can only have 255 columns
in excel....

 
Answer #2    Answered By: Betty White     Answered On: Sep 04

For your first question look at Excel online help:
Fill in a series  of numbers, dates, or other items
1. select  the first cell in the range  you want to fill, and then
enter the starting value for the series.
To increment the series by a specified amount, select the next cell in
the range and enter the next item in the series. The difference between
the two starting items determines the amount by which the series is
incremented.
2. Select the cell or cells that contain the starting values.

3. Drag the fill  handle over the range you want to fill.
To fill in increasing order, drag down or to the right.
To fill in decreasing order, drag up or to the left.

 
Answer #3    Answered By: Beverly Brooks     Answered On: Sep 04

Sure 256 or 255 is the limit. I forgot it but what I mean is a big
number.

When I want to fill, say, 1:1000 in a column  (without adjcent
cells), it is awkward to drag.

The best way I figured is to select  1000 cells first then fill.

Any hints are welcomed.

 
Answer #4    Answered By: Adelfrid Fischer     Answered On: Sep 04

Easiest way to do this.

Place your cursor in the cell you want as your starting number.
Type your starting number.
Make sure you are still in the same cell.
From the Edit menu, select  Fill, and then select Series
The Fill Series dialog box will open.
Set the Series in to Column
Set the Type to Linear
Set the Stop Value to 1000
Set the Step Value to 1. (5 if you want to count by 5: 5, 10, 15, etc)
Click OK

 
Answer #5    Answered By: Barabas Cohen     Answered On: Sep 04


Well... you could always combine the 'goto' key with some keyboard
shortcuts...

Lets assume that your cursor is in A3 and you want to select  the range
from A3:B1003. Touch function key 5 (F5). A 'go to' dialog box will
pop up. Type in A1003 in the reference box. BUT before you touch enter
key or hit the OK button... hold down the SHIFT key. Doing this will
select every cell between A3 and A1003.

 
Answer #6    Answered By: Stefan Thompson     Answered On: Sep 04

irst sentence, second paragraph should have said: ...'A3:A1003.'

 
Answer #7    Answered By: Ivan Coleman     Answered On: Sep 04

When I have a large number of cells in a column  like this, I usually use a
temporary formula:

put 1 in the first cell (call it E10)
put =E10+1 in E11
copy/paste E11 down to all the cells that need the values
select all the cells
copy, then paste special/values to the same place

 
Answer #8    Answered By: Jet Brown     Answered On: Sep 04

To select  a large area, there are several techniques you can use.

Click on the top left cell, then use the scroll bars to expose the bottom
right cell, then shift-click on it.

If your top left cell is at or near the top left of the spreadsheet: Select
the bottom right cell. Then shift-ctrl-home. If your top left cell is not
to be A1, then use shift-right-arrow and shift-down-arrow to move the
selection to your top left cell.

If you only want a few hundred cells: click on the top left cell. Then use
shift-page-down to extend downwards and shift-right-arrow to extend to the
right.

and so on. Adding shift to almost any movement will extend a selection
area.

 
Answer #9    Answered By: Ludkhannah Fischer     Answered On: Sep 04

Often I have to add consecutive ID numbers to data after-the-fact.


1. I add the column  to the left of my data.

2. I enter the value "1" (or my starting number) in A2 where my
records/line items begin

3. I tab the column to the right (cell B2)

4. I use Ctrl+Down Arrow

5. If the data is contiguous (no empty cells) then you will reach the
bottom of the range  in Column B

6. Use Left Arrow to move Column A

7. Now use Ctrl+Shift+Up Arrow to select  all the way to the first
filled cell in Column A

8. Remember it has the value "1"

9. Select fill  from the Edit menu

10. Choose Series ...

11. In the dialog box everything should be set to create 1 to n for the
range you selected. So I won't go into all that.
12. Click OK and you are done.

Tip: Sometimes I will put "x" as a bumper at the bottom or right end of my
range in empty columns and rows. When I want to fill the range with a
formula or other information. All I have to do is CTRL+Shift+Down Arrow (or
right arrow) and I have the selection exactly as I want it.

 
Answer #10    Answered By: Sairish Kauser     Answered On: Sep 04

Another Tip:

If you have filled column  B and would like to continuously number Column A.
In cell 1 type 1, and in cell A2 type in 2. Select both cells. Double
click the fill  handle to extend the series  to the end of Column B. Will
also work in Column C. Just need a column of continuous information for it
to know how far to go.

 
Answer #11    Answered By: Javairea Akram     Answered On: Sep 04

I'm looking to do this same thing (copy formula down to the length
of data in an adjacent column) but with vba. I know how to use 'Range
(ActiveCell, ActiveCell.End(xlDown)).Rows.Count' to get the number
of rows but can anyone tell me the code to fill  the range  with a
formula?

For example, column  A contains a range of data that could be any
lenght from 10 rows to maybe 3000 rows long. I want to use vba to
insert a formula in column B and have it automatically extend to the
length of the data in column A. How is this done?

 
Answer #12    Answered By: Laura Rodriguez     Answered On: Sep 04

Having number of rows known you can try this
You have final row number "r", you can use for loop to fill  the formula in
range
like

for each cell in range  ("C1: C"&r)
c = "your formula" here.
activecell.offset(1,0).select
next

 
Didn't find what you were looking for? Find more on fill series and select a range Or get search suggestion and latest updates.




Tagged: