Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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.

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

in excel....

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.

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

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.

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

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

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.

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.

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.

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?

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

Related Topics:

- fill range not working
- Select Method or Range Class failed
- modify a macro to find the selected range
- Determining used range, selecting and copying
- Select Range, less last row
- Select a Range and one blank row above
- highlight a range using a named range
- Fill List Box Based On Selection On Other List Box
- Fill VBA form with Cell values of an Excel sheet
- How can I fill all combinations of x and y in columns A and B
- Fill Blank rows
- Naming series in multiple Graphs
- Display multiple series of data
- Fill color
- Chart Series
- sum of series of natural numbers
- How is inserting fibonacci series in singly linked list done?
- VBA Excel Coding for dynamically changing function range
- apply function for range of column cells
- VBA variable range in a formula
- Reference range row and column in VBA
- Absolute vs. relative range selection
- Copying Filtered data to a different range
- Variable Ranges, Loop?
- Using a Defined Variable Range in a VBA SumIf