Find # Days fromCurrent Date in Array...VBA Help

ExcelKid_10

Board Regular
Joined
Mar 17, 2004
Messages
87
Hello-

I'm trying to create a code that will search through an array on a worksheet and find a date that is 7 days prior or closest too from the current. Basically, the sheet contains data that is being imported in from an Access query that gets updated weekly. I've tried creating my own For Each loops and searched the message board but to avail. The structure of my sheet is simple: Column A contains all the dates.

Any thoughts would be much appreciated!

Thanks,

EK
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
Hi EK

This code selects in column A dates after (today - 7 days).
It assumes that A1 is the header and that the dates are in a contiguous range from A2 down.

I used autofilter. It's very efficient and avoids looping to search the dates.

Then at the end, loops through the dates found and you can insert there your code.

I hope this contributes to the solution of your problem
PGC

Code:
Option Explicit

Sub FilterDates()
Dim rAllDates As Range, rDates As Range, rC As Range
    
With Worksheets("Sheet1")
    Set rAllDates = .Range("A2", .Range("A" & ActiveSheet.Rows.Count).End(xlUp))
    .Range("A1").AutoFilter Field:=1, Criteria1:=">=" & Date - 7
    Set rDates = rAllDates.SpecialCells(xlCellTypeVisible)
    .AutoFilterMode = False
End With

For Each rC In rDates
    ' insert your code
    MsgBox rC.Address
Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,112,211
Messages
5,541,845
Members
410,550
Latest member
ganeshsamant
Top