Go to the Last Date in a Column

[Douglas Eckert]

I want to build a macro to tell Excel to keep only the latest MonthYr in a
column and to filter out everything else. The overall objective is to
combine this instruction with previous macros I have built so that I 1)Add a
left-hand rank column, 2) Filter all but the latest MonthYr, 3) Sort by
Performance Threshold scores, 4) Rank the scores (using Biff's formula) and
5) Go to the cell containing the name of my organization.

Hints are welcome!

DOUG in Wichita

 

[Bernie Deitrick]

Doug,

Do you have dates in your column, or just month/year strings that are not true dates?

HTH,
Bernie
MS Excel MVP

 

[Douglas Eckert]

Bernie: I have Month Comma Space Year-(in four digits): Apr, 2007.

(Thanks for responding. I am out of the office for the rest of the
afternoon, but hope to resume with this on Monday early).

Doug

 

[Bernie Deitrick]

Doug,

That doesn't actually answer my question, since Apr, 2007 could be a date that is formatted mmm,
yyyy or it could be the string "Apr, 2007"

To tell the difference, select one of the cells. If the formula bar shows the exact same string,
then it is a string. If the formula bar show 4/1/2007 (or some other day in April) then the cell is
a date.

If it is a string, select the column, and press Ctrl-H to bring up the Replace dialog. Under "Find
what:" enter a comma, and under "Replace with:" enter a space, the number 1, and a comma " 1,"
(without the parentheses) and then press OK. This will convert the strings to dates. Then press
Ctrl-1 to bring up the format dialog: on the number tab, select "Custom" in the Category window, and
in the box below Type: enter in mmm, yyyy
and the dates will be formatted to look exactly as they did before.

So now that you have dates (Or if they were dates to start) simply select the column, choose Data /
Filter / Autofilter, and then click the drop down arrow that appears at the top of the column.
Select "(Top 10)", and in the dialog that comes use, reduce the 10 to 1, and the data for the latest
month/year combo will be the only visible data (all the rest will be hidden).

You can record those actions to use in your macro....

HTH,
Bernie
MS Excel MVP

 

[Douglas Eckert]

Bernie: Formatting is "General". I shall work on this a little bit later.
Thank you very much for your suggestions. I shall try them out soon.

Doug