Auto Select Last active Cell in a column

[johnw29]

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.

 

[oldbrewer]

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]

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]

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

 

[FDibbins]

Awesome, happy to help

 

[oldbrewer]

Was I wrong in assuming you wanted to collect data for a number of different pick up points ?