VBA Cut rows with date range

Littlemalky

Board Regular
Joined
Jan 14, 2011
Messages
223
I'm using Excel 2007 with data in columns A:H on a workbook entitled, "Fullfillment - Domestic.xlsx", on the worksheet entitled, "Materials". The columns will remain static; however, the amount of data below will change every week. I've sorted column D in descending order. Column D contains dates. I basically need VBA to cut the sequential rows that contain dates from the current month, the previous month, and all dates beyond the current month (everything in the future).

The days are not my focus when cutting the data, the actual month is my only guide (i.e. today is March 15th, I need to cut everything in February, and everything beyond March). This all needs to be relative to the month we are in as I run this report weekly so I can't just call upon March everytime.

The dates in this column are formatted as such: "m/dd/yyyy".

This cut needs to be pasted to another sheet entitled, "New Releases". Then I need the rows deleted from which we cut on the "Materials" worksheet. Is this possible?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
This works in the UK. You might have to tinker with the date formatting for other regions.

Code:
Sub deleteOtherMonths()
    dt1 = Format(DateSerial(Year(Date), Month(Date), 1), "dd mmm yyyy")
    dt2 = Format(DateSerial(Year(Date), Month(Date) + 1, 1), "dd mmm yyyy")
    Application.ScreenUpdating = 0
    With Sheets("materials")
        Set lr = .Cells(Rows.Count, "A").End(xlUp)
        With .Range("A1").CurrentRegion
            .AutoFilter
            .AutoFilter Field:=1, Criteria1:="<" & dt1, Operator:=xlOr, Criteria2:=">=" & dt2
        End With
        On Error Resume Next
        .Range("A2", lr).EntireRow.SpecialCells(xlCellTypeVisible).EntireRow.Delete
        On Error GoTo 0
        .Range("A1").CurrentRegion.AutoFilter
    End With
End Sub
PS it also assumes your headers are on row 1 - you ought to be able to alter it without much fuss.

HTH

PPS sorry, just noticed I misread your query, re the pasting of the data to another sheet.

I think you could run this then add a bit of code to paste the remaining lines to the right location.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,222,046
Messages
6,163,588
Members
451,846
Latest member
ajk99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top