Is there a quicker way than dargging down a column.

[bygum]

Hello all,

In colums A and B I have a series of dates.Then in column C I place the following function

=(A1-B2)/362.25

This gets the age difference placed in colums C. I then drag down to fill in the rest of Col C to get the rest of the ages.

Is there a way I could do this without having to manually drag down.Sometimes I can have thousands of cells to cover,so it can take quite a while.Also the amount of cells in COL A+B will vary(But both columns will always have corresponding dates in them.)
Any suggestions most welcome
Bygum

 

[My Aswer Is This]

You could run this script and it will do all the work for you"

Sub Get_Ages()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
Cells(i, 3).Value = (Cells(i, 1) - Cells(i, 2)) / 362.25
Next
Application.ScreenUpdating = True
End Sub

The script puts the value in column "C" not the formula.
Is that OK?

 

[Caleeco]

Can you not just select the first cell that contains the caluclation... then double click the bottom right corner of the selected cell?

This should auto-fill down, as long there is data either side of the column with the formula.

Caleeco

 

[thisoldman]

This works for adjacent columns, left or right, with no intervening blank columns. Say your data is in A2:A1000 with no blanks; your formula is in B2.

Method 1
Select B2. Move your mouse cursor over the small square in the bottom right corner of B2. The mouse cursor will change from the thick white cross to a thin black plus sign. Mouse double-click while the cursor is the plus sign and your formula will be copied down to B1000.

Method 2, intervening incomplete column
Your data is in A2:A1000, and your formula is in C2. Copy C2 then select A2. Press Ctrl+DownArrow. Then press RightArrow twice to select C1000. Press Ctrl+Shift+UpArrow to select C2:C1000. Press Ctrl+v to paste.

Method 3 , intervening incomplete column, you know the last cell address
Your data is in A2:A1000, and your formula is in C2. Copy C2, C2 remains selected. Press Ctrl+g to bring up the Go To dialog box. Type C1000 in the Reference box. Press Shift+Enter to close the dialog. C2:C1000 will be selected. Press Ctrl+v to paste.

Method 4, formula not yet entered
You know you want the formula to be in cells C2:C1000. Ctrl+g to use the Go To dialog box. Enter C2:C1000 to select all the wanted cells in column C and close the box. Type the formula but instead of pressing Enter, press Ctrl+Enter. The formula will be copied through the selected range.

There are variations. For instance, pressing End then Arrow consecutively is the same as pressing Ctrl+Arrow keys simultaneously.

 

[bygum]

Many thanks to all,great answers and all of them helped me solve the problem.Great!

 

[My Aswer Is This]

Glad we were able to help you. Come back here to Mr. Excel next time you need additional assistance.

Many thanks to all,great answers and all of them helped me solve the problem.Great!