Delete Rows Based On Date Column
modifying the following from deleting rows older than 365 days to delete rows older than 12 months. So I can keep a rolling year by month.
Sub DeleteOldSR()
Dim x As Long
Dim iCol As Integer
Application. ScreenUpdating = False
iCol = 7 'Filter on column G (Create Date)
For x = Cells(Cells.Rows.Count, iCol).End(xlUp).Row To 2 Step -1
s = Cells(x, 3).Value
If s Like "Closed" Or s Like "Closed w/o Customer Confirm" Then
If Cells(x, iCol).Value < (Date - 365) Then
Cells(x, iCol).EntireRow.Delete
End If
End If
Next
Application.ScreenUpdating = True
End Sub
I tried modifying the IF statement using month with the following but it seems to delete all months < and is not year dependant.
If Month(Cells(x, iCol).Value) < Month(Date) - 12 Then
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Delete Rows Based On Inputted Date
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 Replies!
View Related
Delete Rows Based On Condition Of 1 Column
I would like to Use my Excel VBA program to search each row in a csv document for a name (located in a cell ) if the name exists then I would like to delete the entire row. Whenever I try to do the above in excel, even when I save in csv format the file formating or something gets changed and the file which has to be procesed through another program then process incorectly. Is there a way to do what I need while retaining the formating which must obviously be changed when the file is opened in excel?
View Replies!
View Related
Delete Rows Based On Flag In Column
I want to get a Macro to delete all rows wherever "DELETE" appears in a certain column - I tried using the Delete Entire Row Based on Criteria Macro on Ozgrid but I'm looking for a fully automated solution, rather than the question boxes coming up and asking which cell/criteria to use. The spreadsheets I'm using this on are big, so because this Macro uses filters, it takes a long time and I also need to run this macro on multiple sheets so its not practical to use this. DELETE is just the word I'm using as the TRUE statement generated from an IF formula that I'm using to compare cells in adjacent rows ie =IF(AND(E2=E3,G2=G3,Q2=Q3),"DELETE",FALSE) - Its completely fine for me to copy/paste values of this formula first and then sort the column so that all cells containing DELETE will appear at the top of the column if that helps. Any ideas on how I could get a Macro to delete those rows at this stage?
View Replies!
View Related
Delete Duplicate Rows Based On 1 Column
creating a macro for an excel spreadsheet? The problem I am faced with is I need to reduce a xls file from a ticketing system at work that contains roughly 50,000 rows. What I need to have done is reduce a Site Code column (column B) so that for each ticket entry there is only one occurrence of each user that worked on that ticket.What the macro should do is to look through column B and remove any duplicate rows for the same ticket number located in column A. once the macro gets to the next ticket number in column A I need it to start over on the duplicate check for column B. I have a attached a xls file to this post to try and show what it is i am working on.
View Replies!
View Related
Delete Non Numeric Rows Based On Column
The attached picture shows part of an excel file. It contains about 50.000 rows which I want to run a pivot on. The problem is though that the file contains some rows which stop my pivot from running correctly. These data which I mention are in rows 4, 7, 22-25. Seeing as deleting them manually would take me hours and hours I was wondering if there would be a macro which I can run which delete rows automatically. I think it should filter on the following: whenever there is not a numeric value in column A, the entire row should be deleted.
View Replies!
View Related
Vba To Delete Rows Based On Certain Conditions In Column A
I have a report in which I need to delete the entire row for each cell in Column A that has the name "Defacto" in a certain location in that cell. I am trying to use VBA code in conjuction with the "MID" function [i.e., Mid(Cell.Value, 8, 7) = "Defacto"]. This is the code I came up with (but, obviously, it doesn't work): Sub DeleteRowOnCondition() Range("A2").Select Range(ActiveCell, ActiveCell.End(xlDown)).Select For Each Cell In Selection If Mid(Cell.Value, 8 , 7) = "Defacto" Then Rows.Delete Next Cell End Sub As well, there is another worksheet in the same report in which I need to delete all the rows that do NOT meet that condition (while retaining the ones that do).
View Replies!
View Related
Search Column Bottom Up Then Delete Rows Based On Value Found
I've attached a dummy worksheet that shows constant values of 1.00 in columns D and E. In the actual workbook, user action will sometimes cause these values to change to something other that 1.00. The user may wish to delete the action that caused the change. I need a macro that will search from the last cell in column D upward to the first instance of a cell that is not equal to 1.00 and select that cell and those immediately above with the same value as the first found cell. For example, in the attached worksheet the search would start in the last cell of Columd D and search upward. It would find cell D23 and upward to D19. These entire rows would be deleted.
View Replies!
View Related
Delete Duplicate Rows Based On Cell/column Values?
I have a excel file which contains dublicate rows. The duplicate rows can be identified based on few cell/column values. I need a macro to delete the duplicate rows when the below condition is satisfied: let us consider row 5 and row 6: If column 7,12,13,16,17,18,19,23,24,27,28,29,30 in row 5 = row 6 then row 6 has to be deleted. This condition has to be followed for all other rows in the excel used range. Have attached the sample workbook.
View Replies!
View Related
Delete Rows Based On Criteria While Referencing Column Header
I'm trying to use VBA code to delete certain rows based on a couple of criteria as follows: 1) If column header is "TERMINAL NAME" and the cell value in that column is "BONDDESK", I need the entire row deleted. 2) Similiarly, if the column header is "PRODUCT TYPE" and the cell value in that column is "CORP", that entire row needs deleted as well. The information is coming from another source, so the columns may change from day to day, i.e, "TERMINAL NAME" may be in Column L one day but in Column O another day.....
View Replies!
View Related
Code To Delete Rows Based On Status Code In Column
I'm trying to write a VBA script which will delete all rows in my Excel spreadsheet where Column I (which contains a status code) does not contain the word "Completed". At the moment, I'm doing this the other way round: my script is able to search for entries in Column I which contain the status codes "Pending", "Awaiting Authorisation", "In Progress" etc and delete them. The idea is that when all those rows are deleted, I'll only be left with rows which have a status of "Completed". This works fine at the moment. However, the concern is that if a brand new status code is added to the data file, my script would be unable to pick it up and delete it. This is a small sample of the code I'm currently using (which deletes all the rows with statuses other than Completed):
View Replies!
View Related
Delete Rows If Cell Contains Date Outside Date Range
I've found similar queries on the site, but none seem to handle multiple dates, and my attempts so far have been futile! Requirement: 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 Replies!
View Related
Delete Rows Based On Criteria & X Rows Below
This sheet has A:K columns and 1:3212 rows. There are 'page headers' that are in the text file that I want to delete (the text file was exported from an AS400 program). The first row that starts the page header has SA341 in column 1. Each page header has 5 rows. I used this code from one of the other threads on deleting rows, but I obviously do not understand the code as it deleted all rows that contained SA341. Sub DeleteRows() Column_To_Check = 1 Start_Row = 1 End_Row = ActiveSheet. Cells(Rows.Count, Column_To_Check).End(xlUp).Row MsgBox End_Row Search_String = "SA341" For Row_Counter = End_Row To Start_Row Step -1 If ActiveSheet.Cells(Row_Counter, Column_To_Check).Value < SA341 > Search_String Then ActiveSheet.Rows(Row_Counter).Delete End If Next Row_Counter End Sub
View Replies!
View Related
Delete Rows Based On Values In Rows Below
I am copying a price list from a worksheet. I currently have a script that deletes unwanted rows (products) but these products' header rows' are left. I also want to delete these text based headers. One solution might be a script that reads a columns cell value in the row(s) below and if values are missing the header row should be deleted.
View Replies!
View Related
Date Based Delete
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 Replies!
View Related
Delete Rows Which Have Date 7 Days Old
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 Replies!
View Related
Delete All Rows Except Most Recent Date
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 Replies!
View Related
Delete Rows Outside Date Range
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 Replies!
View Related
Delete Rows Based On Criteria ..
I am a pharmacist that runs reports and I need to sort and exclude data It is in Excel 2003 format I run a report that includes a patients name in one column, the medications name in another column and if the medication was withdrawn, wasted, returned or restocked in another column. Withdrawn, wasted, returned or restocked are treated as different functions and each have their own row entry. Patient and medication name are the same in all situations I need a macro that will look at the patients name and medication name, see if it is the same and delete all rows that have a withdrawal with an associated wasted, returned or restocked.
View Replies!
View Related
Delete Rows Based On Criteria?
I had a raw data sheet in which i need to prepare a statement just like the attached worksheet. My requirement is to delete all those rows in the department column except the department which starts with "C" Just like "CNN" & "CNN-IN". Rows with data containing the words starting with "CNN" should not be deleted .The rows can contain words with "CNN" or "CNN*"(here * denotes anything after the word CNN) I had just formatted the whole worksheet for easy reference.Actually the raw data is extracted from other program which is very clumsy & irregular. The department column might be in any column.
View Replies!
View Related
Delete Rows Based On Content
Can a macro be used to: 1. Delete rows that contain certain text in a worksheet ? 2. Highlight a cell a colur based on a response ? I have attached an example of what i mean ..... In the Audit Protocol worksheet is the main information - which contains questions and answers .... If a yes, n/a or no is selected then a response is automatically generated in the observations column (thanks to SHG for helping me with the formula for this) ..... How do I then get the cell to change to red if a NO response only is selected ? Then the information from the Audit Protocol worksheet (the observation column and number column) are copied across to the worksheet named Action List (this is done just by the copy function) .... How do I go about deleting rows that contain "no action required" - as these are not needed for the report to be generated ?
View Replies!
View Related
Delete Rows Based On Rules
I am trying to clear row with the following rules: the last cell must not contain 0, if 0 delete entire row. the first cell the the A row should be only three character long, if more delete entire row. Now I managed to delete the 0 in the last cell and but cannot get the VBA to count the characters in the first row and if more than three, delete the entire row . I know to count the characters in a cell is done by Len(Rng.Value) , but getting the VBA right is alluding me . the script is here, Sub Del_rows_with_zero_in_column_of_activecell() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Const StartRow As Long = 1 'Row to Start looking at Dim StopRow As Long Dim Col As Long
View Replies!
View Related
Delete Rows Based On Values
I have a 20,000 row text file that I import. It has blank rows, page headers and footers, and many rows at the bottom that I do not want. I have been cleaning up the data manually but this takes a great deal of time and I have a need to perform this clean up on a more frequent basis. I have tried recording a macro to do some of the deleting but I am running into an issue that I do not know how to over come. When I filtered the data based on "Blanks" on a specific column and then try to delete them in mass, Excel errors out stating that my request was too complex.
View Replies!
View Related
Delete Rows Based On Condition
complete this coding? Sub DeleteBlankRowInColumnA() 'this macro scrolls down Column A if the cell is empty the row is deleted. Dim A As Integer A = 0 Do Until A = 142 If Range(0, "a1") = 0 Then Selection.Delete shift:=xlUp End If Loop End Sub
View Replies!
View Related
Delete Rows Based On Two Columns
I have a spreadsheet that in column A has either the letters "C", "LP", or is blank. In Column L it has either numbers 1 through 100, or the letter "D". What I need it to do is to check and see if the data in column L is "D" and in the same row column A is blank, then delete that row. Also, if the data in Column L is "D" and the Data in column A is either "C", or "LP", then I need it to change the "D" to "1". It might be easier to do it separately because once you run the part that deletes the rows that dont have a C or LP then I could probably just do one that says if column L = D then change to 1. Lets see if I can simplify this for you. If Column L = D and Column A = BLANK then Delete Row (this would delete all Ds that dont have the C or LP) THEN If Column L = D then change to 1
View Replies!
View Related
Delete Rows Based On 2 Criteria
Is there a vba code that can delete the entire row based on two criterias? If a row has a location of Canada and is a female, then delete the entire row. Name Location Gender Alan Mexico Male Dick USA Male Sharon Canada Female <-- This will be deleted if based on criteria Mike Canada Male
View Replies!
View Related
Delete Rows Not Matching Date Variable
I have a need to call a sheet from another sheet, copy two pages from the newly opened, existing sheet to a new workbook, search one sheet of the new workbook for any entries that don't contain a variable I specify, which is the first four digits of the field, and delete them. The sheets represent a daily view of jobs (identified by the "RptDate" variable) and an entire monthly list of jobs, which I want to delete all of them not from the date specified in the variable "RptDate". The fields in column A contain the date in "mmdd" format as the first four digits, and I have that specified in the "B1" celll of the originating workbook. I found some code in this link Search Column Delete Row If Value Found that looked good. I had to modify it since I needed to search for a variable (RptDate) and needed to search column A and start on row 8 of that column. My current code looks like this: Sub Macro1() Dim RptDate As String Dim RptMonth As String Dim iLastRow As Long Dim i As Long RptDate = Range("B1").Text RptMonth = Range("B2").Text Workbooks.Open Filename:=(RptMonth) Sheets( Array(RptDate, "Total Database")).Copy iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 8 Step -1 If Left(Cells(i, "A").Value, 4) <> RptDate Then Rows(i).Delete End If Next i End Sub It works fine right up to the "Rows(i).Delete" line. It errors out with a "run-time error 1004, delete method of range class failed" and highlights the above line. I know the fields I'm pulling the variables from are working right since it opens the correct workbook off of one of them.
View Replies!
View Related
Delete All The Date From Column
.Rows(1).AutoFilter .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 Replies!
View Related
Delete Rows Based On Fill Color
i found a link to my problem in the subject title below, http://www.ozgrid.com/forum/showthre...t=45580&page=2 The guy at the end of the post says he got it working in the end, but no one seems to have posted the final solution - could someone tell me if the solution is actually in the thread, and if so where! My problem is outlined by this guy - basically i want to be able to delete rows in a range that have no fill color, and so keep the ones that are colored.
View Replies!
View Related
Delete Rows Based On Two Criteria Within Each Row
I have a sheet with say 1000+ rows that is a QA report of possible mistakes found in a employee rostering tool. The report finds all occurrences where the staff member only has one coffee break rather than the two they are entitled to. However, on days where they have training, or other half day commitments, on of the coffee breaks is not entered into out tool. This means we are getting a whole lot of entries that we do not require(i.e. one coffee break error). The tool uses icons but the cells do have a single unique character under each icon type. For example, the coffee break cells have a "T" and training cells have a "&" underneath the icon (without quotes). The staff members day is separated into 15 minute blocks and each block occupies a cell in a column. This means each staff members day spans many columns in a row (i.e. C=8am, D=8:15am, E=8:30am etc...). What I have been trying to accomplish is to create a macro in VB code that will locate any row with both a "T and a "&" and delete it. This will eliminate occurrences that we are aware of and leave only genuine errors. There are other combination's that I would like to include also such as: "T" "[" "T" "#" "T" "@" "T" "]" The first row is headers and the first two columns contain team names and staff names which I am trying to exclude (because names contains "T"'s ). I have spent many hours now looking for example code on Google, this forum and other forums, however most of the examples I have found are looking for two criteria within a column or specific criteria that is not suitable to my application (i.e. values <> certain numbers etc...).
View Replies!
View Related
Delete Rows Based On User Input
1) Allow user input of a number between 1 and 999 2) Search column three and delete any rows that don't match this number. I have some code that would delete any rows within a certain column, based on predefined criteria and i've tried modifying it to suit my needs.
View Replies!
View Related
Delete Duplicate Rows Based Only 2 Columns.
I need to filter the following file. I need to remove all rows where COL A value and COL B value are the same. COL C does not need to be considered. However I need to retain one of the Col C values for purposes of formatting. The end result should look similar to columns F,G and H!
View Replies!
View Related
Code To Delete Rows Based On Condition
I have an Excel spreadsheet that is given to me weekly, but I'd like to remove repetitive and empty rows before presenting it to someone. It is set up like this: Row 1, A1, contains the word Project. Row 2 is blank Row 3 is blank Row 4 is blank Row 5, A5, contains the word Organization. Row 6 is blank Row 7, A7, contains a 6-digit number starting with 3. Row 8 is blank (all of the above starts over again (loops) approximately 30 times) The final row contains the phrase "Grand Total" *Row 5 is repetitive and is not required. I'd like to delete it. I have too many empty columns. Getting rid of them (and Row 5) would greatly shorten my spreadsheet. I am aiming for: Project 301111 Project 301112 Project 301110 (Repeat until finished) Grand Total:
View Replies!
View Related
Delete Rows Based On Duplicates In X Columns
I've been looking through various forums looking for a macro for a particular task, but I have not found something that works yet. Maybe someone could help me out? It's pretty straightforward situation. I need a macro that removes an entire row when: information in column B and column C are both found in other rows ( duplicates). I'm dealing with a worksheet with store record information ... Store Name (column B), Street Name (column C), State/Province, etc. I import new store records all the time and many are invalid because they share the same name and street address and therefore need to be removed. The ones that Ive played with unfortunately end up deleting the cells only and not the entire row. It's a tedious process to do manually remove entire rows (for duplicate column B&C) when dealing with thousands of rows and I would very much appreciate any assistance or insight into how simplify the process with a macro. I've attached a sample of what I'm working with.
View Replies!
View Related
Delete Rows Based On Cell Contents
I have merged two workbooks into one. What I need to do at this point is to delete all rows that have a duplicate entry, basically anytime the cell content in one cell matches the cell content in the cell right below or above it, BOTH rows should be deleted. At this point, this is above my VBA skills so I'm asking for help in how to do this. The stripped version of the workbook is attached (only 100 rows) but in reality this is a huge workbook with almost 22,000 rows. You will notice in the attached workbook, that cell contents for A2 and A3 match. For what I need to do, I need both rows (2 and 3) to be deleted. If you go down a bit, starting in row 89 all the contents in column A are unique so those need to remain.
View Replies!
View Related
Delete Rows Based On Partial Match
I currently have a speadsheet that looks similar to the following: REF DATA1 DATA2 SVRW_67 784598 475395u SVRW_34 fdsjsjdf 734978 CAT_56 ghdrhad gaghadh CAT_67 48578 8943539 I'm trying to create a macro that will delete all rows with "CAT" in the REF column. I've searched this site and struggled trying to adapt other methods listed and got nowhere.
View Replies!
View Related
Filter And Delete Any Rows Where There Is A Value In A Column
I have written some code that will allow me to filter and delete any rows where there is a value in a column ( column J in this instance ) more than 2500. The problem is that it only wolrs when i press F8 and step through the code, and not when i press the commnd button and run it. Sheets("CAIZOLY9").Select Range("A1").Select Do Until ActiveCell = "Payment Amount" ActiveCell.Offset(0, 1).Select ActiveCell.Select Loop Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Select If ActiveCell > 2499.99 Then Selection.EntireRow.Delete Else End If Loop Range("A1").Select End Sub
View Replies!
View Related
|
TRACKER
