Auto Select Last active Cell in a column

johnw29

New Member
Joined
Mar 12, 2016
Messages
2
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.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,993
pointdate3point103/02/201613/02/20163.3
point103/02/20164point204/02/201616/02/20163.0
point204/02/20168point304/02/201617/02/20161.6
point304/02/20168point405/02/201619/02/20161.8
point405/02/20167point507/02/201619/02/20161.7
point507/02/2016
point107/02/2016average frequency
point206/02/2016
point307/02/2016
point407/02/2016
point308/02/2016
point410/02/2016
point510/02/2016
point309/02/2016this macro finds the earliest and latest dates for each point
point410/02/2016
point510/02/2016easy then to count how many times each point is visited
point311/02/2016maybe with a simple pivot table
point413/02/2016
point513/02/2016I used countif in col G
point412/02/2016
point513/02/2016For j = 1 To 5
point113/02/2016 For k = 2 To 31
point214/02/2016 If Cells(k, 1) = Cells(j, 8) Then Sum = Sum + 1: GoTo 100
point316/02/2016 GoTo 200
point316/02/2016100 If Sum = 1 Then firstdate = Cells(k, 2): lastdate = Cells(k, 2)
point415/02/2016If Cells(k, 2) < firstdate Then firstdate = Cells(k, 2)
point516/02/2016If Cells(k, 2) > lastdate Then lastdate = Cells(k, 2)
point216/02/2016200 Next k
point317/02/2016Cells(j, 9) = firstdate: Cells(j, 10) = lastdate
point419/02/2016Sum = 0
point519/02/2016Next j
End Sub

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

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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
Joined
Mar 12, 2016
Messages
2
Thank you both for your responses, for my purpose the simple MAX(F:F)-MIN(F:F) works great!
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,993
Was I wrong in assuming you wanted to collect data for a number of different pick up points ?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,423
Messages
5,444,374
Members
405,278
Latest member
karen1

This Week's Hot Topics

Top