vba help - advanced filter

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
837
Office Version
  1. 2010
Platform
  1. Windows
Hi Team,

I am using advanced filter to filter and extract data. Criteria is Date , Date columns comes in text.
Convert text date into Date , Find greatest date of Column A , and filter via advanced filter.

Macro giving correct output, But I am not sure whether it creates any problem in future.

is find and replace right approach for converting text to Date format?...


VBA Code:
Sub ExtractLatestTrade()

Dim dt As Date

Dim rngData As Range
Dim rngCriteria As Range
Dim rngOutput As Range

Dim rngDay1 As Range
Dim rngDay2 As Range

Set rngDay1 = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set rngDay2 = Range("d2", Range("D" & Rows.Count).End(xlUp))

'Convert from string into Date format
With rngDay1
    .Replace What:="/", Replacement:="/", LookAt:=xlPart
    .NumberFormat = "MM/DD/YYYY"
End With

dt = Format(Application.Max(rngDay1), "MM/DD/YYYY")
Range("H2").Value = ">" & dt 'greater than


With rngDay2
    .Replace What:="/", Replacement:="/", LookAt:=xlPart
    .NumberFormat = "MM/DD/YYYY"
End With

'Store all range for advanced filter.
Set rngData = Range("d1").CurrentRegion
Set rngCriteria = Range("H1").CurrentRegion
Set rngOutput = Range("M1:O1")


rngData.AdvancedFilter xlFilterCopy, rngCriteria, rngOutput

End Sub



Book3
ABCDEFGHIJKL
1TradeDateTradeDateAmountSalesManMax Date Column -ATradeDateAmountSalesMan
209/06/2009/04/20121Dhoni09/08/20368Yuvraj
309/07/2009/04/20325Sachin09/08/20202Virat
409/07/2009/04/20333Dhoni09/08/20347Yuvraj
509/07/2009/06/20477Brett Lee09/08/20216Brett Lee
609/07/2009/07/20388Dhoni09/08/20376Brett Lee
709/07/2009/07/20380Sachin09/08/20262Brett Lee
809/04/2009/07/20155Sachin
909/04/2009/07/20320Brett Lee
1009/06/2009/07/20326Macgra
1109/06/2009/08/20368Yuvraj
1209/08/20202Virat
1309/08/20347Yuvraj
1409/08/20216Brett Lee
1509/08/20376Brett Lee
1609/08/20262Brett Lee
Sheet2



Thanks
mg
 

Attachments

  • Snapdate Date Column.PNG
    Snapdate Date Column.PNG
    5.9 KB · Views: 0

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
I don't understnd why you are replacing a character with the same character. Other than that, the NumberFormat should not cause any problems.
You might want to take a look at this: Type Conversion Functions - Visual Basic
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
837
Office Version
  1. 2010
Platform
  1. Windows
Hi JLGWhiz,

Thanks for your help, I have replaced a charecter with same charecter, it helps in converting text to Date
and to extract max date ( through which I can filter data and extract new records greater than max date.)

Thanks
mg
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
you're welcome,
Regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,122,418
Messages
5,596,028
Members
414,039
Latest member
southike

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