VBA filter for removing prior month data

[DJhuffman]

Hello all! I've been engaging in a crash course of Excel macros ever since I was reassigned to help my department streamline data processing. I've done fairly well, but at this point, there are two steps in the process that I haven't figured out how to code.

For the purposes of the example, let's say I have a set of dates in column D and balances in column K. The procedure involves filtering out items with dates from the prior month (Ex. March dates in April) so they are not caught up in the procedures to remove resolved items per Col K balances. Essentially, I need the VBA equivalent of going into the filter and deselecting the prior month (assume that autofilter is already active per the VBA code). And of course, I can't just be month specific, since the requirements will change each month.

Is there a fairly uncomplicated method to do this?

Thanking you in advance!

 

[JLGWhiz]

I am not sure what you mean by deselecting. Normally a filter will produce items you want to do something with. However, here is some code that would filter anything from the previous month based on dates in column D. But I suspect what you might want is to filter the current month's data and work with that. At any rate, this will give you an idea of how it is done.

Sub t()
Dim sdt As Date, edt As Date
sdt = Month(Date) - 1 & "/1/2019"
edt = GetLastDayOfMonth(Date - 28)
ActiveSheet.UsedRange.AutoFilter 4, ">=" & sdt, xlAnd, "<=" & edt
ActiveSheet.UsedRange.Offset(1).SpecialCells(xlCellTypeVisible).'copy, delete, whatever'
'Other code here to complete process
End Sub


Public Function GetLastDayOfMonth(ByVal myDate As Date) As Date
    GetLastDayOfMonth = DateSerial(Year(myDate), Month(myDate) + 1, 0)
End Function

The first procedure would run to set the filter and it calls the second procedure to get the 'edt' date parameter used for the filter process.

 

[DJhuffman]

Thanks for your help. What you gave me was a perfect stepping stone. Your code yielded the data for the prior month only, which allowed me to determine the inverse to generate all the data prior to the prior month (The current month is already removed in a prior step, so formulas to retain it's data were unnecessary. In case you're interested, here's what I ended up with:

Sub datetest()


Dim sdt As Date
sdt = Month(Date) - 1 & "/1/2019"
ActiveSheet.UsedRange.AutoFilter 4, "<" & sdt


End Sub

This filters for all dates prior to March. Admittedly, it guts a lot of the code you provided, but don't get me wrong. I'm very grateful for your help. At this point, I guess my concerns revolve around the use of "/1/2019". Is this going to need to be updated around January (1-1 & "/1/2019")?

Also, I'm not overly familiar with the UsedRange property. What exactly is it doing?

Again, thank you for your help.

 

[JLGWhiz]

Thanks for your help. What you gave me was a perfect stepping stone. Your code yielded the data for the prior month only, which allowed me to determine the inverse to generate all the data prior to the prior month (The current month is already removed in a prior step, so formulas to retain it's data were unnecessary. In case you're interested, here's what I ended up with:

Sub datetest()
Dim sdt As Date
sdt = Month(Date) - 1 & "/1/2019"
ActiveSheet.UsedRange.AutoFilter 4, "<" & sdt
End Sub

This filters for all dates prior to March. Admittedly, it guts a lot of the code you provided, but don't get me wrong. I'm very grateful for your help. At this point, I guess my concerns revolve around the use of "/1/2019". Is this going to need to be updated around January (1-1 & "/1/2019")?

Also, I'm not overly familiar with the UsedRange property. What exactly is it doing?
Again, thank you for your help.

No problem, I didn't expect you to use the code as I wrote it. It was just to illustrate how to get the dates and use them. Glad you worked it out.
The UsedRange property will return a range from the cell at top left of all cells with data or formulas to the intersecting cell for the last row with data or formulas and the last column with data or formulas. Just what it says, the range that you are using, including any blank cells that fall within.
Regards, JLG