Delete Rows Based On Date Column
May 10, 2008
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 6 Replies
ADVERTISEMENT
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
Mar 2, 2006
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 9 Replies
View Related
Oct 19, 2007
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 9 Replies
View Related
Mar 12, 2008
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 6 Replies
View Related
Mar 14, 2008
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 9 Replies
View Related
Jun 12, 2008
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 8 Replies
View Related
Nov 29, 2006
How can I delete an entire row based on seperate column data? Example: I want Excel to look at cell A2 and cell E2. If the values are the same I want Excel to delete row 2.
View 4 Replies
View Related
Jun 23, 2014
I am building a template ("Table") that will import data from 4 other spreadsheets and then format the data once its all in the template. I need to delete all rows where a name didn't import. The names are landing in column B (starting with B22), so I set up my code using an active cell loop macro to examine each cell to see if it was empty, and then to delete the row if it were. I've tried 4-5 iterations of code but nothing is working correctly.
Apparently when the fields are copied over from the other spreadsheets, some empty cells actually have something in them such that they are not completely blank. What syntax I can use so that I capture every instance of a blank/empty cell and delete that corresponding row? Some of the code I've tried is below.
[Code] .....
View 3 Replies
View Related
Jul 7, 2009
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 5 Replies
View Related
Oct 28, 2009
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 5 Replies
View Related
Jun 6, 2007
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 6 Replies
View Related
Apr 2, 2007
I wish to count the number of entries in a workbook based on the entries in two columns. The columns are for example H1:H100 ( date) and L1:L100 (status). I want to the number of times the date in H1:H100 is over 30 days old, as well as L1:L100 = 'Started' for example.
View 2 Replies
View Related
Aug 22, 2014
In one spreadsheet, I want to have a command button that will hide all rows where the date column (column A) shows a date older than one week from today. When this button is clicked again, all rows will unhide again. Preferably the Command button title would change to reflect whether it is on the hide or show cycle (for example "Click to Hide all older than one week" and then "Click to Show all events") .
View 9 Replies
View Related
Jul 24, 2009
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 4 Replies
View Related
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
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!
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 5 Replies
View Related
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()
Columns("F:F").Select
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"
[code]....
View 2 Replies
View Related
Mar 5, 2008
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 9 Replies
View Related
Jun 20, 2008
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 2 Replies
View Related
Dec 8, 2009
I have a sheet where i want to delete duplicate rows where column A and column B combined are equal, i.e. range(Ax:Bx) where x is the current row. I am using the macro below but cant seem to get it working as I keep getting a type mismatch error and Im not sure why.
View 2 Replies
View Related
Jul 30, 2014
Have have a excel spreadsheets with 2 columns. One with the employees name and one with the date that their warning expires. I would like the employees name to be automatically removed after the date expires. How do I do this.
View 1 Replies
View Related
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
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
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
Nov 24, 2007
I have a column of dates (B2:B1500) that I want to compare to a single date (A1).
If the dates in Column B are older than the date in A1 I want to delete the entire row.
View 3 Replies
View Related
Mar 10, 2008
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 6 Replies
View Related
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
Jan 28, 2014
I need to build a macro which copies 3 rows every day and pastes the row data into an identical sheet. The three rows will have column "D" as =today(). As the days progress the three rows will change accordingly ( tag to the today's date)
e.g. 28/1/2014
28/1/2014
28/1/2014
I need the macro to recognize the date when pressed and copy the corresponding rows of data and paste them into an identical sheet with the same date. The second sheet is an archive sheet. The date will tick over as per the calendar.
View 9 Replies
View Related
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