Date Selection

[Guest]

I have a macro that ends with a resulting list of parts. One column has a
Date Added column and I want to select specific dates within it. I want
parts with a date of the last 18 months selected and deleted. Now I'm not
certain what the code would look like in the macro. Is there a way to select
everything with the date of the last 18 months?

The date is in this format:

1/1/2006 = January 1, 2006
12/12/2006 = December 12, 2006

etc.

Now I could put it in there probably to select the last 18 months from this
date, though that would hardly be automated, because next month I would have
to go into the code and modify it, then the next month, and so on. I wanted
a universal way of doing it so the last 18 months regardless of the month I'm
in will be selected and deleted. make sense?

Thanks for the help!!!!

 

[Guest]

Nice ambiguous date examples. It is easy to see whether your dates are m/d
or d/m

In the same spirit, turn on the macro recorder and apply an autofilter to
your data. Select the appropriate criteria. Delete the visible rows.

Now turn off the macro recorder and look at the recorded code.

Replace the Criteria with something akin to

= format(DateSerial(year(date),month(date)-18,day(date)),"m/d/yyyy")

From the immediate window for demo:
? format(DateSerial(year(date),month(date)-18,day(date)),"m/d/yyyy")
12/27/2005

 

[Guest]

Two issues, first:

= format(DateSerial(year(date),month(date)-18,day(date)),"m/d/yyyy")

The code bugs out on "m/d/yyyy" It highlights the m and says that it should
be at the end of the statement. When the quotes are removed, it doesn't bug
out but it appears to search for it, unfortunately it doesn't find anything.
So what am I missing here?


Second potential Issue,

After checking the format of the column in question I noticed that it is not
a "true" date, or appears not to be. The original date information from
whence the data came had to be formatted into a readable form. The original
data would look as this:

0697 when imported into excel. For easy reading, I wanted to turn that
data into a readable date. The issue was that upon import from the other
souce, the data was not readable as a date by excel, thus it couldn't be
simply formatted. SO:

I had to use this formula

Dim rngCell As Range
Dim rngStart As Range
Dim intMaxRow As Integer
Dim intCtr As Integer

With ActiveSheet
intMaxRow = .UsedRange.Rows.Count
Set rngStart = ActiveCell
For intCtr = 1 To (intMaxRow - 1)
Set rngCell = rngStart.Offset(RowOffset:=intCtr)
If IsNumeric(rngCell) And rngCell.Value > 0 Then
On Error Resume Next
rngCell.Value = DateValue(Int(rngCell / 100) & "/1/" & rngCell Mod
100)
rngCell.NumberFormat = "mmm - yy"
On Error GoTo 0
End If
Next intCtr
End With
Set rngStart = Nothing
Set rngCell = Nothing

to turn it into

6/1/1997

Does that make any sense?

SO, I guess I'm at square one, is there something I'm doing wrong or being
too difficult?

Thanks again!!!

 

[Guest]

I already demo'd to you that the format command works. The immediate window
result is a successfully executed VBA statement. I am not sure how you are
testing it, but apparently not correctly or in the proper context. (the equal
sign is superflous and was just to show it used at the end of the criteria1
statement although it would probably be concatenated with a Less than, less
than or equal to, Greater than, or Greater than or Equal to sign based on
your description. Working with dates and an autofilter can be troubling
with various regional settings, so it may take some experimentation on your
part, but it doesn't appear you have gotten that far.

Perhaps you are relegated to using the
DateSerial(year(date),month(date)-18,day(date)) portion of my answer and
looping row by row through your data making the decision to delete or not.

If
rngCell.NumberFormat = "mmm - yy"

correctly formats the cells, then the value is stored as a true date serial
number. The alternative is it is stored as a text string, but number
formats don't work on text strings.

So it appears the ball rests in your court to clean up your data and
determine how you will use the information imparted.