Auto Select Last active Cell in a column

J

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.
 
Sort by date Sort by votes
[TABLE="width: 1075"]
<colgroup><col><col><col span="5"><col><col><col><col span="3"><col><col></colgroup><tbody>[TR]
[TD]point[/TD]
[TD]date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]point1[/TD]
[TD="align: right"]03/02/2016[/TD]
[TD="align: right"]13/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point1[/TD]
[TD="align: right"]03/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]point2[/TD]
[TD="align: right"]04/02/2016[/TD]
[TD="align: right"]16/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point2[/TD]
[TD="align: right"]04/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD]point3[/TD]
[TD="align: right"]04/02/2016[/TD]
[TD="align: right"]17/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point3[/TD]
[TD="align: right"]04/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD]point4[/TD]
[TD="align: right"]05/02/2016[/TD]
[TD="align: right"]19/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point4[/TD]
[TD="align: right"]05/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD]point5[/TD]
[TD="align: right"]07/02/2016[/TD]
[TD="align: right"]19/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point5[/TD]
[TD="align: right"]07/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point1[/TD]
[TD="align: right"]07/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]average frequency[/TD]
[/TR]
[TR]
[TD]point2[/TD]
[TD="align: right"]06/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point3[/TD]
[TD="align: right"]07/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point4[/TD]
[TD="align: right"]07/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point3[/TD]
[TD="align: right"]08/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point4[/TD]
[TD="align: right"]10/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point5[/TD]
[TD="align: right"]10/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point3[/TD]
[TD="align: right"]09/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]this macro finds the earliest and latest dates for each point[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point4[/TD]
[TD="align: right"]10/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point5[/TD]
[TD="align: right"]10/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]easy then to count how many times each point is visited[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point3[/TD]
[TD="align: right"]11/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]maybe with a simple pivot table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point4[/TD]
[TD="align: right"]13/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point5[/TD]
[TD="align: right"]13/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]I used countif in col G[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point4[/TD]
[TD="align: right"]12/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point5[/TD]
[TD="align: right"]13/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]For j = 1 To 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point1[/TD]
[TD="align: right"]13/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] For k = 2 To 31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point2[/TD]
[TD="align: right"]14/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"] If Cells(k, 1) = Cells(j, 8) Then Sum = Sum + 1: GoTo 100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point3[/TD]
[TD="align: right"]16/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] GoTo 200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point3[/TD]
[TD="align: right"]16/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]100 If Sum = 1 Then firstdate = Cells(k, 2): lastdate = Cells(k, 2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point4[/TD]
[TD="align: right"]15/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]If Cells(k, 2) < firstdate Then firstdate = Cells(k, 2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point5[/TD]
[TD="align: right"]16/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]If Cells(k, 2) > lastdate Then lastdate = Cells(k, 2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point2[/TD]
[TD="align: right"]16/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]200 Next k[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point3[/TD]
[TD="align: right"]17/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Cells(j, 9) = firstdate: Cells(j, 10) = lastdate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point4[/TD]
[TD="align: right"]19/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sum = 0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]point5[/TD]
[TD="align: right"]19/02/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Next j[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]End Sub[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
to get the earliest date, yo6u could use either SMALL(range,1) or MIN()...
[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][/tr]
[tr][td]
1​
[/td][td]
1/1/2016​
[/td][td][/td][td]
1/1/2016​
[/td][td]
1/1/2016​
[/td][/tr]

[tr][td]
2​
[/td][td]
1/2/2016​
[/td][td][/td][td]
1/10/2016​
[/td][td]
1/10/2016​
[/td][/tr]

[tr][td]
3​
[/td][td]
1/3/2016​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
4​
[/td][td]
1/4/2016​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
5​
[/td][td]
1/5/2016​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
6​
[/td][td]
1/6/2016​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
7​
[/td][td]
1/7/2016​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
8​
[/td][td]
1/8/2016​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
9​
[/td][td]
1/9/2016​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
10​
[/td][td]
1/10/2016​
[/td][td][/td][td][/td][td][/td][/tr]
[/table]
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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
How can I exactly count NETWORKDAYS in multiple date ranges, after a specific date?
Replies
1
Views
497
Tetra201
Tetra201
R
  • Solved
Select method of Range class failed | Selecting a range
Replies
4
Views
117
rachel06
R
M
  • Question Question
Populate a table of items based on 1 cell selection
Replies
1
Views
252
Edgar_
Edgar_
E
  • Solved
Add duration based on selected date range
Replies
6
Views
112
etaf
etaf
L
  • Question Question
Selection based on two variables - copy and paste to another sheet
Replies
3
Views
450
kvsrinivasamurthy
K

Forum statistics

Threads
1,226,906
Messages
6,193,596
Members
453,810
Latest member
Gks77117

Share this page

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
Back
Top