# Auto Select Last active Cell in a column

#### johnw29

##### New Member
Hi,

I am trying to find out the frequency with which we service certain neighborhood recycling points. In one column I have the dates of service listed, and the next column I have this.... =(F6-F3)/count(F3:F500) formula to figure out the frequency. F6 is the most recent date, however, I am trying to enter into the formula a way to automatically select the last active cell in the column so I do not have to manually change the formula every time I enter a new date. By the way, the (F6-F3) counts the days between the last and first dates, and the count(F3:F500) is counting how many active cells there are to figure out how many pick ups have occured. Thanks for any help and advice.

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### oldbrewer

##### Well-known Member
 point date 3 point1 03/02/2016 13/02/2016 3.3 point1 03/02/2016 4 point2 04/02/2016 16/02/2016 3.0 point2 04/02/2016 8 point3 04/02/2016 17/02/2016 1.6 point3 04/02/2016 8 point4 05/02/2016 19/02/2016 1.8 point4 05/02/2016 7 point5 07/02/2016 19/02/2016 1.7 point5 07/02/2016 point1 07/02/2016 average frequency point2 06/02/2016 point3 07/02/2016 point4 07/02/2016 point3 08/02/2016 point4 10/02/2016 point5 10/02/2016 point3 09/02/2016 this macro finds the earliest and latest dates for each point point4 10/02/2016 point5 10/02/2016 easy then to count how many times each point is visited point3 11/02/2016 maybe with a simple pivot table point4 13/02/2016 point5 13/02/2016 I used countif in col G point4 12/02/2016 point5 13/02/2016 For j = 1 To 5 point1 13/02/2016 For k = 2 To 31 point2 14/02/2016 If Cells(k, 1) = Cells(j, 8) Then Sum = Sum + 1: GoTo 100 point3 16/02/2016 GoTo 200 point3 16/02/2016 100 If Sum = 1 Then firstdate = Cells(k, 2): lastdate = Cells(k, 2) point4 15/02/2016 If Cells(k, 2) < firstdate Then firstdate = Cells(k, 2) point5 16/02/2016 If Cells(k, 2) > lastdate Then lastdate = Cells(k, 2) point2 16/02/2016 200 Next k point3 17/02/2016 Cells(j, 9) = firstdate: Cells(j, 10) = lastdate point4 19/02/2016 Sum = 0 point5 19/02/2016 Next j End Sub

<colgroup><col><col><col span="5"><col><col><col><col span="3"><col><col></colgroup><tbody>
</tbody>

#### FDibbins

##### Well-known Member
to get the earliest date, yo6u could use either SMALL(range,1) or MIN()...
 A​ B​ C​ D​ 1​ 1/1/2016​ 1/1/2016​ 1/1/2016​ 2​ 1/2/2016​ 1/10/2016​ 1/10/2016​ 3​ 1/3/2016​ 4​ 1/4/2016​ 5​ 1/5/2016​ 6​ 1/6/2016​ 7​ 1/7/2016​ 8​ 1/8/2016​ 9​ 1/9/2016​ 10​ 1/10/2016​
C1=SMALL(A:A,1)
D1=MIN(A:A)

Likewise you could use LARGE or MAX for the latest date...
C2=LARGE(A:A,1)
D2=MAX(A:A)

#### johnw29

##### New Member
Thank you both for your responses, for my purpose the simple MAX(F:F)-MIN(F:F) works great!

#### FDibbins

##### Well-known Member
Awesome, happy to help

#### oldbrewer

##### Well-known Member
Was I wrong in assuming you wanted to collect data for a number of different pick up points ?

Replies
9
Views
158
Replies
5
Views
67
Replies
3
Views
118
Replies
7
Views
114
Replies
1
Views
38

Threads
1,132,805
Messages
5,655,406
Members
418,197
Latest member
Sumit_Vikram

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back