Filters with Excel Macro

Question

Filters with Excel Macro

Navigation

#1 by (1 votes)

0

I hope you can help me with the following.

I have a sheet in Excel, in which I need to make certain types of filters.

Criterion 1: Column A; It contains dates and I need to filter the most recent date. The date is in this form "Month and Day, example September 12".

I know how to set Autofilters with criteria in macro but with dates I could not filter the last date.

Example filter with criteria filtering the value 10 of column AG that is No 33:

Sheets("Nombre de la hoja").select
ActiveSheet.Range("$A:$AG").Autofilter Field:=33, Criteria:="">=10" , Operator:=xlAnd

Any ideas, just to filter the last date?

These are my data:

This is your code that I added to my Macro:

 Sub FechaMax()

Dim FechaMax As Range

Sheets("CalcPromedios").Select
Sheets("CalcPromedios").Activate

With Application.WorksheetFunction
       Set FechaMax = Range("A" & .Match(.Max(Range("A:A")), Range("A:A"), 0)) 'cambiar A por la columna que tiene las fechas
End With


ActiveSheet.Range("$A$1:$AG$65536").AutoFilter Field:=1, Criteria1:=FechaMax.Value 'cambiar el área de aplicación y el número de field
Set FechaMax = Nothing

End Sub

And I uploaded an example to my drive: link

DS

vba excel-2013

asked by Jose Daniel Solis 18.09.2018 в 21:56

source

1 answer

Problems with header () in an iteration to check the login ERR_HTTP_HEADERS_SENT - Json Web Tokens in Node.js

score 1 · Answer 1

The most recent date will be the highest value date in your entire column of dates. You can calculate, and then look for your position within the column. Once the first cell containing the most recent date is located, then the filter will be applied.

A simple example that I have made:

The most recent date would be 09/09/2018, which appears 2 times. My code:

Dim FechaMax As Range

With Application.WorksheetFunction
       Set FechaMax = Range("A" & .Match(.Max(Range("A:A")), Range("A:A"), 0)) 'cambiar A por la columna que tiene las fechas
End With


ActiveSheet.Range("$A$1:$B$10").AutoFilter Field:=1, Criteria1:=FechaMax.Value 'cambiar el área de aplicación y el número de field
Set FechaMax = Nothing

And the result after executing the macro, is that it filters me by more recent date:

I hope you can adapt the code to your needs.