vba code for filtering by latest date

[Rudolf14]

Hey guys,

I'm new to the VBA code thing...still in the record macro stage!!
I need assistance.

I have a worksheet that contains 6 columns,
A: Section
B: Machine
C: Component
D: Severity
E: Comment
F: Date.

Now I need a VBA code to filter by the latest date.

 

[Rudolf14]

Hey guys,

I'm new to the VBA code thing...still in the record macro stage!!
I need assistance.

I have a worksheet that contains 6 columns,
A: Section
B: Machine
C: Component
D: Severity
E: Comment
F: Date.

Now I need a VBA code to filter by the latest date.

NB. I forgot to mention, I have a code for sorting out the data, Now I need a code for the filter part. Thanx in advanced

 

[footoo]

https://www.mrexcel.com/forum/excel-questions/70642-most-recent-date-auto-filter-within-macro.html

 

[lrobbo314]

Try this.

Sub FilterMaxDate()
Dim R As Range: Set R = Range("A1").CurrentRegion
Dim MaxDate As Date: MaxDate = Application.Max(R.Columns(R.Columns.Count))


R.AutoFilter Field:=6, Criteria1:=MaxDate
End Sub

 

[Rudolf14]

HI Irobbo314,

Thanx for the code. how ever I tried it but it keeps on giving me the date in the first row. You will see I edited the code..

Sub FilterMaxDate()
Dim R As Range: Set R = Range("F:F").CurrentRegion
Dim MaxDate As Date: MaxDate = Application.Max(R.Columns(R.Columns.Count))


R.AutoFilter Field:=6, Criteria1:=MaxDate
End Sub

 

[lrobbo314]

What range is your data in? Can you paste some sample data?

 

[Rudolf14]

HI Irobbo314,

Thanx for the code. how ever I tried it but it keeps on giving me the date in the first row. You will see I edited the code..

Sub FilterMaxDate()
Dim R As Range: Set R = Range("F:F").CurrentRegion
Dim MaxDate As Date: MaxDate = Application.Max(R.Columns(R.Columns.Count))


R.AutoFilter Field:=6, Criteria1:=MaxDate
End Sub

And the other problem I have is that there is a lot of data on the machine that has the same date.
Here is a sample of what I have.

Section	Machine	Component	Severity	Date
P33	CM0147	CM\BOOSTER\PUMP	NORMAL	2018/07/18
P33	CM0147	CM\RH\SEC\CON\REDR	NORMAL	2018/05/14
P33	CM0147	CM\RH\SEC\CON\REDR	NORMAL	2018/07/18
P33	CM0147	CM\RH\SEC\CON\REDR	NORMAL	2018/04/25
P33	CM0147	CM\RH\SEC\CON\REDR	NORMAL	2018/06/03
P33	CM0147	CM\L\RE\CUTT\GBX	NORMAL	2018/07/18
P33	CM0147	CM\L\RE\CUTT\GBX	NORMAL	2018/06/03
P33	CM0147	CM\L\RE\CUTT\GBX	NORMAL	2018/05/14
P33	CM0147	CM\L\RE\CUTT\GBX	NORMAL	2018/04/25
P33	CM0147	CM\RH\PRIM\CON\REDR	NORMAL	2018/06/03
P33	CM0147	CM\RH\PRIM\CON\REDR	NORMAL	2018/07/18
P33	CM0147	CM\RH\PRIM\CON\REDR	NORMAL	2018/04/25
P33	CM0147	CM\HYD	BORDERLINE	2018/07/18
P33	CM0147	CM\HYD	NORMAL	2018/06/04
P33	CM0147	CM\HYD	NORMAL	2018/06/03
P33	CM0147	CM\HYD	NORMAL	2018/05/14
P33	CM0147	CM\HYD	BORDERLINE	2018/04/25
P33	CM0147	CM\L\GATH\ARM	NORMAL	2018/05/14
P33	CM0147	CM\L\GATH\ARM	NORMAL	2018/06/03

<colgroup><col span="4"><col></colgroup><tbody>
</tbody>

And this is what I want:

Section	Machine	Component	Severity	Date
P33	CM0147	CM\BOOSTER\PUMP	NORMAL	2018/07/18
P33	CM0147	CM\RH\SEC\CON\REDR	NORMAL	2018/07/18
P33	CM0147	CM\L\RE\CUTT\GBX	NORMAL	2018/07/18
P33	CM0147	CM\RH\PRIM\CON\REDR	NORMAL	2018/07/18
P33	CM0147	CM\HYD	BORDERLINE	2018/07/18

<colgroup><col span="4"><col></colgroup><tbody>
</tbody>

 

[footoo]

Assuming the dates are in column F :

Dim myDate As Date: myDate = Application.Max(Columns(6))
ActiveSheet.AutoFilterMode = False
Columns(6).AutoFilter Field:=1, Criteria1:="=" & Format(myDate, [F2].NumberFormat)

 

[Rudolf14]

Assuming the dates are in column F :

Dim myDate As Date: myDate = Application.Max(Columns(6))
ActiveSheet.AutoFilterMode = False
Columns(6).AutoFilter Field:=1, Criteria1:="=" & Format(myDate, [F2].NumberFormat)

Dude!!!!! You are a ledgent....THANX a Mill!!!!!

 

[footoo]

The code was available in the link provided in post #3