Macro To Delete Row By Date?

Jun 4, 2013

I am going to record a macro to clean up a report but the first thing I need to do is delete rows that contain dates before July 17, 2012. The dates are in column B. Is this possible in the macro?

View 5 Replies


Macro - Delete Row If Column Has Certain Date

Feb 15, 2010

I generate a report in excel of about 63000 rows (this differs) there are several columns with dates, I wish to remove all the rows with a date before and after certain dates.

It seems I would need a loop for that, but it is unclear to me how this works, so far I have this:

Sub TestMacro2()
Selection.NumberFormat = "m/d/yyyy"
Selection.NumberFormat = "m/d/yyyy"
ActiveWindow.FreezePanes = True
For Each cell In Selection
If Date < 1 - 3 - 2009 Or Date > 2 - 3 - 2010 Then
cell.EntireRow.Delete shift:=xlUp
End If
Next cell
End Sub

View 13 Replies View Related

Macro To Delete Previous Date

Jun 23, 2007

I need a macro to loop through a column and delete any date prior to todays date, as well as all cells in the deleted date's row.

View 2 Replies View Related

Delete Duplicates With Macro Keeping Only Newest By Date

Feb 14, 2011

I have a spreedsheet with 17,000 rows in it. In column D I have ID numbers and in column B I have the dates. There are multiple entries for each ID in column D which gives multiples dates each entry was given in column B. I need a VBA Macro that will identify duplicate entries in Column D and keep only the most recent entry by date in column B.

View 9 Replies View Related

Excel Macro Delete Rows Where Date Criteria Is In Another Sheet

Feb 28, 2014

I am trying to adapt a macro which deletes all rows in Worksheet 1 where the date in column K differs from a date in Worksheet 2. The date in Worksheet 2 will change each day.

I think the macro below could be used, but don't understand how to replace the "Value = ":70:" with a reference to the master date in Worksheet 2.

Sub test() Dim lr As Long, i As Long lr = Range("A1").End(xlDown).Row
For i = lr To 1 Step -1 If Cells(lr, 1).Value = ":70:" Then
Cells(lr, 1).EntireRow.Delete End If lr = lr - 1 Next i End Sub

View 2 Replies View Related

Macro To Delete Cell Contents If Date In One Cell Bigger Than Date In Another Cell

May 9, 2012

I have the following columns that return cover period dates.

Start of Cover PeriodEnd of Cover Period01-Nov-0631-Jan-0701-Jul-0831-May-0901-Dec-0930-Jun-1001-Jan-1131-Dec-10

I however need a macro that will delete the cell contents if the "Start of Cover Period" (column AK) date is > than the "End of cover period" (column AL) date.

Thus it must compare ak2 with al2 and if ak2>al2 then clear both cells. if AK2

View 2 Replies View Related

Macro Delete Page (delete A Sheet In A Macro But When I Run The Macro)

Jun 19, 2009

I want to delete a sheet in a macro but when I run the macro, I always get a message warning and I have to answer the msg box to delete the page. Below is the macro command I am using.


View 4 Replies View Related

Popup Or Userform Enter Date And Delete Rows Containing That Date

Nov 26, 2013

I wondering if it's possible to create a code to have a popup where the user can enter a date in mm/dd/yyyy format and then all rows containing that date will be deleted.

View 9 Replies View Related

Delete Rows If Cell Contains Date Outside Date Range

May 29, 2008

I've found similar queries on the site, but none seem to handle multiple dates, and my attempts so far have been futile!

I press a button on Sheet1 and am prompted to enter a start date and an end date.

Depending on the dates I enter, all rows on Sheet2 which fall outside the Start/End date range will be deleted.

All dates to be checked against on Sheet2 are in a single column, and all rows are populated, there are no blanks. Dates are in the format dd/mm/yy

View 5 Replies View Related

VBA Delete Rows If Date Is Less Than Defined Date

Feb 26, 2014

I have tracker, where Cells in Column F have dates or are blank.

I am trying to run a macro to delete all rows when the following criteria apply:

for all cells in column F, if date < 1-Oct-2013 then entire row should be deleted if cell is blank - nothing should happen This is what I got so far - but it is not working...I have entred the date "10/1/2013" in cell AA2 but macro should always check against that date (not variable) so it might not be needed.

Sub PART3_Delete_old_team_members()
Selection.NumberFormat = "m/d/yy;@"
Dim endrow As Integer
On Error Resume Next
endrow = Sheets("Global Team List").Range("F900").End(xlUp).Row
Searchdate = "AA2"


View 2 Replies View Related

Macro To Delete Vba Code - Macro Security Warning

Feb 7, 2008

I have recently grabbed the very helpful "Macro to delete VBA code" from this site, and it's working but with one small problem.

My worksheet runs a lot of code, then deletes all macro code and saves itself.

The problem is the next time i open I still get the macro security warning!

I've checked thoroughly and there is definatley no vba left anywhere.

If I open the document, enable macros, and save it, then open it again, I no longer get the warning.

View 9 Replies View Related

How To Delete A Word In Date

Jan 24, 2013

I have a file in which the data is written in cell.

1g Injection Total
10mg Tablets Total
1.5% w/w Gel Total

i want the to delete the word "Total" from each cell so that i can use the vlookup formula to get the desired result from another file.if there is any solution.looking forward for the an easy and understandable reply.

View 4 Replies View Related

Row Delete When Date Reached

Mar 26, 2014

i have a sheet called match and dates in column f and would like the rows to auto delete when 6 years old

View 5 Replies View Related

Date Based Delete

Jun 26, 2008

i have a large set of data, with a data in column a & column e & some text in column F

i need macro to remove rows where the date in col a is earlier than 6 months before the date in column e & column f has the text "a/g or "g"

View 9 Replies View Related

Delete All The Date From Column

Feb 10, 2009

.Rows(1).AutoFilter Field:=1, Criteria1:=wsStart.Cells(2, "B")
.AutoFilter.Range.Range("B:C").Copy wsStart.Range("D1:E1")
.AutoFilterMode = False

It deletes all the data in columns D and E from row 3 onwards

View 9 Replies View Related

Delete Row If Date Is NOT Between 2 Dates

Aug 21, 2009

I have defined 2 dates ( Starting and Ending ).

Sub Define_Dates()
begdate = InputBox("Please enter the Beginning Date in MM/DD/YY Format")

enddate = InputBox("Please enter the Ending Date in MM/DD/YY Format")
End Sub

I need to delete any row (from LR to FR Step -1) that Col M ( Date ) is outside the Beginning and Ending Date.

View 9 Replies View Related

Delete Cells After Specified Date

May 7, 2007

VBA code to delete cell values say from B10:B100 to K10:K100 after a certin date and time. Like cell A 10 will have this date and time.

View 9 Replies View Related

Delete Both Sets Of Duplicate Date?

Oct 8, 2008

I currently have about 3000 rows of data, much of it being duplicate data. Is there a way to have it search for duplicate data, and have it delete BOTH sets of duplicate data? For example, say this is what is in my spreadsheet:....

I would want it to delete both John Smith records and both Ted Nugent records, leaving Bob Bobbert's and Michael Jordan's records.

View 4 Replies View Related

Delete Row If Column Contains Date Greater Than 1/0/00

Aug 20, 2014

I have an Workbook where on Sheet one it shows a date of a sold item in Column AH. On sheet two Column AG references sheet 1 Column AH. On sheet 2 I need it to delete the row if the date is greater than 1/0/00 ( which is an empty cell in sheet 1, but for some reason shows it as "1/0/00"). I have tried this code but I keep getting errors:

[Code] .......

View 8 Replies View Related

Delete All Rows Except Most Recent Date

Jan 26, 2009

Have a sheet with 24K rows. Column A is client name. Column B are check dates. The info is sorted by client then check date. Clients can have one to many check dates, so I client is listed as many times as there are check dates. If ten checks, then ten rows for that client. One check then one row for that client.

I only want to keep the last/most recent check date for each client, deleting all other rows for that client with check dates before the most recent. I believe I need to step through the file comparing column A, row by row, to see if I have a match for the next row in A. If no match I keep that row, because there is only one check, and move down one. If a match, then I delete the current row.

How to code this is my problem. Is it a loop?

View 6 Replies View Related

Delete Unique Values After Date X

Apr 7, 2009

I need is for the below code to not delete anything if the date is within a certain range. I want to keep all rows which have a date of 11 days past the current date. So for example. If today is 04/07/2009 then the macro should only delete unique values from 04/19/2009 and on.

Column A is the Item # and column D is expiration date.

View 3 Replies View Related

Delete Rows Which Have Date 7 Days Old

Jan 29, 2010

I was wondering if somebody could help me with a little bit of code?

When the sheet is opened, I would like some code to search down column A, starting from cell "A5" and delete all rows which contain a date which is greater than 7 days ago.

The dates are in order from the most recent being at the top.

For example:

Today is 29/01/2010, I would like all rows which have a cell in column A containing Today-7() to be deleted. 28/01/2010 to stay. 19/01/2010, delete row.

Hope that makes sense?

View 6 Replies View Related

Delete A Row Based On A Date Stored Within It

Jan 19, 2012

I have a table that extends from A1 to S630 (the number of rows changes as records are added and deleted).

The function to add rows based on the date is fine and I have implemented, but I cannot figure out how to delete a row based on a date.

The date is entered on a sheet called Calendar in Cell J20

so Sheets("Calendar").Range("J20")

and the date is in column B of the table, which has been made using the Table function

Is there a way of scanning through the whole table deleting all the rows with the date entered into J20 on the calendar sheet.

View 5 Replies View Related

Separate And Delete Date Into New Cells

Jan 25, 2012

I have a table that I've imported into excel. Below are 2 samples of the information in the cells..


Basically I would like to separate into 3 columns as such

From A1;
D1 = Unspecified
From A2;
D2 = U

When Importing I can't use delimited or fixed width. I can do this with Find and Replace and copying cells, but thought there might be a more savvy way to do this.

View 6 Replies View Related

VBA To Delete Records Based On Certain Date

Jul 18, 2012

I have a spreadsheet titled "PMIX" In Columns A:F Column F has Week Ending date. Each week, I need to update this, i.e. delete the data from two weeks ago and then add the new weeks data. What I am thinking is I enter a Week Ending Date in H1, then I would like VBA to delete all rows where it has the data in H1. I am hoping for a delete function as opposed to a clear so I can shift all the rows up.

View 3 Replies View Related

Delete Rows Outside Date Range

Apr 26, 2006

On sheet2 Range L4:L2000 I have starting dates entered in mm/dd/yy format. On sheet2 Range M4:M2000 I have finish dates entered in mm/dd/yy format.

On sheet3 I have a numeric value of mmddyy (no brackets) in cell BC4.

What i'm looking to do is if the numeric value on sheet3 BC4 does not fall between the start and finish dates on sheet2, then the entire row should be deleted. Can this be done with the different date formats (Brackets vs no brackets)

View 6 Replies View Related

Delete Row If Date Meets Condition

Jun 19, 2008

I have a spreadsheet with about 225 sheets or so containing data. The date is in column J. I am needing to delete all the Rows that have a date greater than or equal to 06/06/08 and loop through all of the sheets.

I recorded a macro to find the date 6/6/2008, then highlight the row, then select the next 450 rows and delete them and it worked ok, but I don't know how to get it to loop through the sheets to the end.

View 2 Replies View Related

How To Delete Cells In A Range Based On Date

Oct 21, 2013

I have a range (D5:BU5) that I will have various dates in each cell. At the end of the row is a percentage block. It has the formula count=(D5:BU5)/70 which gives me a percentage complete. The fun begins where some dates are based off annual, semi-annual, and quarterly. What I want to do is be able to delete cell values so they are not reflected in the final percentage. I know there is a marco that deletes the entire row but that is too much I just want a target cell.

View 3 Replies View Related

Excel 2010 :: Delete Row If Cell Contains Date Or Any Value

Jan 6, 2014

I came across this excellent code here on this site but it does the opposite of what I need. This code deletes empty or blank rows if the cell in column I is blank. I actually need to delete the row if the cell in column I has a date or any data. Using Excel 2010.

Sub deleteBlankRows()
On Error Resume Next
End Sub

View 5 Replies View Related

Delete Rows Based On Inputted Date

Dec 19, 2006

I need to have a User input a date via an InputBox, and then based upon that date, delete rows based on values in Column G. For instance, if a user inputs 12/25/2006, I need to delete all rows that have the date that is less than 12/25/2006 in Column G. There will never be more than 500 Rows of data, and there is data in Columns A thru Z

View 4 Replies View Related

Copyrights 2005-15, All rights reserved