filter text column containing date

Danny54

Active Member
Joined
Jul 3, 2019
Messages
295
Office Version
  1. 365
Platform
  1. Windows
how do i using vba filter a row of data containing dates.

When i set a breakpoint the filter is showing a text string of < 43961 which was converted from code below. If i mouse over the oPDT field it shows a date of "5/10/2020"

Dim myDate as Date
Dim oPDT As String

myDate = "06/09/2020"
oPDT = Format(DateAdd("d", -30, myDate), "MM/DD/YYYY")

.AutoFilter Field:=7, Criteria1:="<" & oPDT

data in column 7
01/01/2019
06/01/2020
02/03/2018

after filtering i would expect to find the 01/01/2019 and 02/03/2018 but nothing is showing up

Thanks
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Maybe the below, set the range to suit

VBA Code:
    Dim myDate As Date
    Dim oPDT As Long

    myDate = DateValue("06/09/2020")
    oPDT = DateAdd("d", -30, myDate)
    With Range("A1").CurrentRegion
        .AutoFilter Field:=7, Criteria1:="<" & CLng(oPDT)
    End With
 
Upvote 0
Thanks for the reply.


let me go after this a different way. Below is a snipit of my data found in column "G".

It show two blank rows,
1 heading,
1 blank row,
1 date,
1 blank row,
1 date,
1 blank row and then
1 date


This entire column is text

how could i code something in VBA that would change those found that contain text(datevalues) to date(datevalues)?

Thanks


Release Date
06/04/2020
06/04/2020
06/04/2020
 
Upvote 0
Try selecting the column, click Data - Text to Columns, click next, check that there is nothing in the Other box and click next, in the Date section select the date format that your data is in and click finish.

Then test the code that I posted.
 
Upvote 0
Thanks for the prompt response.

I have over 40 files being opened and cycled one at a time. The vba code always worked until the input sheets changed that column from date field to a text field. I was hoping to somehow to just change that text field back to a date for the whole column and then process. Sorta looks like im striking out.
 
Upvote 0
Did you try what I put in my last post? you can just record your actions if you want it in a macro.
 
Upvote 0
yes, i did that yesterday and found that I would have to modify the macro created to handle variable sheets as each sheet being read in is somewhat different.
I was hoping for a better solution. (Still don't know all the slick ways of doing things)

I did try something that is a bit kludgy a few minutes ago.

In my code

inserted a new column "A"
inserted a formula =IFERROR(DATEVALUE(H1),0) into "A" and copied down
changed my Autofilter to used this column

then after the filter was executed -
deleted the entire column "A"

Not very pretty but it might do the job

Thanks for the help
 
Upvote 0
macro created to handle variable sheets as each sheet being read in is somewhat different.
Rather than what is different about the sheets what is the same? for instance the column header names?
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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