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

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
Joined
Apr 11, 2010
Messages
11,004
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,004
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,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

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