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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
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

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
Was I wrong in assuming you wanted to collect data for a number of different pick up points ?
 

Forum statistics

Threads
1,136,639
Messages
5,676,931
Members
419,659
Latest member
ShalDRH

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

Which adblocker are you using?

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
Top