VBA - Delete Rows based on Date criteria from second sheet and Paste in New Rows from third sheet

banneduser123

Banned - Rules violations
Joined
Mar 29, 2006
Messages
181
i'm new to macros but I'm decent at understanding so have gotten a fair amount of simple stuff done recently. mostly off amending recorded macros.

let me explain my file. i don't know how to add spaces or borders in here and i just spent like 10 minutes googling that. anyway, there are 3 columns: Report Date, Section, and CUSIP.


Sheet 2
Report Date | Section | CUSIP
-----------------------------------
8/30/2019 | US Sales | XYZ
8/30/2019 | US Sales | ABC
8/30/2019 | Foreign Sales | 123
8/30/2019 | Foreign Sales | 456
8/30/2019 | Foreign Sales | 789
8/30/2019 | Foreign Sales | 1011
7/30/2019 | Foreign Sales | 1213
7/30/2019 | US Sales | EFG
7/30/2019 | US Sales | HIJ
7/30/2019 | US Sales | KLM
7/30/2019 | US Sales | NO14
7/30/2019 | Foreign Sales | 1516P

In cell A1 of Sheet_1, there is a Date i want to reference to. For this example, let's say Sheet_1 A1 = 9/30/2019.

Now, i need the macro to look at the data in Sheet_2 Column A and delete every single row that has a date older than my reference Date by at least two months.

So, if my reference date in Sheet_1 A1 is 9/30/2019, then I need every line deleted on Sheet_2 that has the date of 7/29/2019 or before that.

There are thousands of rows of data. So, what would be the most efficient way of doing this?

Also, each month's data will likely be blocked together in groups...meaning i won't have alternating rows of one month to another.

Essentially, this is just removing data that is 2 months old from the sheet.

After it's done with that, i need the macro to go to Sheet_3, and copy the data from row 2, until the last row that has data in it. next, it then pastes values into the first blank row of data in Sheet_2.

Hopefully that is clear. Basically, in other words, I have a sheet that has two months of data in it. As indicated by the Date in column A. Each month, I need to go to this sheet, remove the data that is two months old, and paste in the data that is new (aka the value in cell A1 of Sheet_1).


further clarification: so the data on Sheet_3 contains the most recent month's data. so it would only contain data for 9/30/19 in this example. yes, import everything from Sheet_3, except the headers, so takes the data from row 2 until the last row of data. yes, Sheet_2 table is sorted by data. so you'll have like 400 rows of data for 8/30/19, and then 400 rows of data for 7/30/19

any help is greatly appreciated
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
How about somethink like this? It uses autofilter to hide the data less than two months old and deletes the rest.
Code:
Sub rishijain11()
    Dim d As Date
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim sh3 As Worksheet
    
    Set sh1 = Worksheets("Sheet_1")
    Set sh2 = Worksheets("Sheet_2")
    Set sh3 = Worksheets("Sheet_3")
    
    d = sh1.Range("A1")
    d = d - 1
    d = DateSerial(Year(d), Month(d) - 2, Day(d))
    With sh2
        .Activate
        .Range("A1:A3").EntireRow.Insert
        .Range("A1").Value = "Report Date"
        .Range("A2").Value = "<=" & d
        .Range("A4").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
            Range("A1:A2"), Unique:=False
        .Range("A4").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .ShowAllData
        .Rows("1:3").Delete
        sh3.Range("A2").CurrentRegion.Offset(1, 0).Copy .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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