Compare Dates Against One Date & Delete Rows

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


ADVERTISEMENT

Compare Dates In Rows - Delete Then Shift Up

Mar 14, 2012

I am fitting 2 sets of data to match eachother. I want to have as much data as possible but only for the same dates of each dataset.

I have 4 coloumns.
Dataset 1: A is date B is data
Dataset 2: C is date D is data

I want to compare A vs B one row at a time. If they are not similar it should delete and shift up the values in A+B or C+D according to the lowest value (date) of A vs B.

Remember that if the macro just deleted and shifted up it should compare the same (undeleted) value against the new value that just shifted up in the other coloumn.

View 2 Replies View Related

Compare Dates To Date Range & Return Date Based On Outcome

Jul 1, 2008

I need to compare three cells of random dates shown in Column E, F,& G with Row's H5:AK5, H7:AK7, H9:AK7 (the Dates to these rows is Static on row H3:AK3.) EX: ROW 5 has Start Date, End Date (1) and End Date (2). Compare Cell H3 between Start Date & End Date (1). If H3 falls between or equal to Start and End Date(1) then highlight cell H5. Proceed till AK3 (higlighting only the cells H5:AK5). Then compare cell H3 between or equal to End Date (1) and End Date (2) (higlighting only the cells H5:AK5). Then do the same for ROW 7 and ROW 9.

To make things a bit more difficult I need to have " WK#" in Row 14 (these WK# is on another tab called "Task" of the workbook) needs to be displayed in Row's H4:AK4, H6:AK6, & H8:AK8. EX: Compare Date in D15 between or equal to Start Date & End Date (1) then display Wk# in D14 in H4. Continue till all dates in
D15:Z15 are compared to Start Date & End Date (1) and WK#'s in Row D14:Z14 are inputted if applicable in Row H4:AK4, H6:AK6, H8:AK8. I hope this is not confusing. I can't seem to use the upload option so here is alink to download a jpg of the sheet

View 2 Replies View Related

Compare & Confirm Dates In Column To Date In Cell

May 21, 2008

I am trying to do a macro that looks to see if the date is different from one cell to another going down in rows. I got it to work until the cell is empty then it says the date dont match but I just want it to stop the loop. so it should go down a list check to see if the date is the same all the way down the list, stopping if cell is blank, if not give a message box, if it is the same stop.

Is As follows

Dim rowNum As Integer, colNum As Integer, currCell As Range

rowNum = ActiveCell.Row
colNum = ActiveCell.Column
rowNum = rowNum + 1
'get first cell
Set currCell = ActiveSheet.Cells(2, 3)
'loop while cell not empty
Do Until currCell.Value = " "
If currCell.Value = ActiveSheet.Cells(2, 3) Then
If currCell.Value = "" Then
End If

View 4 Replies View Related

VB Code To Compare/delete Rows Of Data

Feb 4, 2007

VB code to carry out the following task?

Look at name in cell BC2, check the name in cell BA2 and if is the same delete the name in BA2 and BC2.

If is different then leave both cells as they are and move onto checking BC3 against BA3
carry this loop thru BC2 : BC40

View 9 Replies View Related

Compare Columns & Insert / Delete Rows

Feb 22, 2008

I have a large data file. In column A is 8760 entries (every hour of every day of the year). Columns D & E are linked, Column D is the time and date whilst Column E is a parameter assocaied with that time and date.

There should also be 8760 entries in these columns, however there are some missing data lines in columns D & E. I would therefore like to move the rows in columns D & E so they match with the entries in column A. Can anyone help? I have a headache as i have over 70 files to complete this operation on.

View 9 Replies View Related

Compare 2 Columns Delete Rows With Matching Values

Feb 5, 2009

I've got two workbooks, Workbook1 with a list in column a and Workbook2 with a list in column F. I want to compare the cells in these columns and delete the entire row in Workbook2 if there is a match.

View 3 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 Rows Outside Of Two Dates?

Mar 31, 2014

I have a list with start dates. These go back about 2 years and forward about 2 years.

What Im after is deleting anything older than say 30 days and anything in future more than say 120 days.

I have found various codes with varying success. This one works a charm....

[Code] .....

the problem is, I dont want the text boxes and would rather I had something like a =today() - 30 OR = today() + 120.

View 5 Replies View Related

Delete Rows Containing Old Dates/times

Apr 1, 2009

I need help deleting rows which contain a cell with date / time in the format dd/mm/yyyy hh:mm if the value is older than the current date / time

any ideas?

View 6 Replies View Related

Compare 2 Columns & Delete Rows Ouside Scope & Show Formula Result In Message Box

Jan 22, 2008

I have the following code to compare two columns and delete adjacent rows if 1 is greater than or equal the other...

Sub LastReceipt_GT_Confirmed()

Dim intLstRow As Integer

For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1
With Range("E" & intLstRow)
If .Value > .Offset(0, 1).Value Then .EntireRow.Delete
End With
Next intLstRow
For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1 .............

View 9 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

Compare Date: Compare The Date From The User Input And The Date Listed

Jan 18, 2007

I need to compare the date from the user input and the date listed on excel. How can I compare it? Is it correct? lngCmp = Val( Cells(I, 31))

Dim lngBegin As Long, lngEnd As Long, lngCmp As Date, lngResults As Long
lngBegin = 9 'beggining of data
lngEnd = 232 'end of data
lngCmp = InputBox("Please enter the date", "Begining of the week")

Lngcmp1 = DateAdd("d", 1, lngCmp)
lngCmp2 = DateAdd("d", 2, lngCmp)
lngCmp3 = DateAdd("d", 3, lngCmp)
lngCmp4 = DateAdd("d", 4, lngCmp)
lngCmp5 = DateAdd("d", 5, lngCmp)

'lngCmp1 = lngCmp + 1
'lngCmp2 = lngCmp + 2
'lngCmp3 = lngCmp + 3
'lngCmp4 = lngCmp + 4
'lngCmp5 = lngCmp + 5
lngResults = 0
lngResults1 = 0
lngResults2 = 0..................................

View 2 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!

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

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()
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

Copying 1 Row With A Date Range To X Rows With Individual Dates?

Aug 19, 2013

I have an Excel file containing data about employees. Each row contains a lot of information that is relevant for a specific date range, denoted by the columns "start date" and "end date". I would like to copy each row so that I have it accessible per date that is within the date range. The only columns that would change in the copy are the date columns. For example, for 1 row that has a date range of 01/08/2013 - 05/08/2013, I want to copy the row 5 times except the date columns become "01/08/2013", "02/08/2013", etc.

View 2 Replies View Related

Deleting Rows When Dates Exceed Current Date?

Aug 4, 2014

How do I delete rows, with the maturity date exceeding the current date ("A10"). Note here that every time I open the excel sheet, the current date ("A10") would change, so I intend to write a VBA file to ensure that new rows are deleted when the dates expire.

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 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 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 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

Delete Rows Not Matching Date Variable

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

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 View Related

Export Data To Sheet And Then Delete Rows NOT Contain Today Date

Jan 25, 2013

I have found some excellent code that exports rows to individual sheets based on values in a column, and it works perfectly. I have found some code that deletes any rows that do not contain today's date:

VB:

Dim LR As Long, i As Long
Application.ScreenUpdating = False
LR = Range("O" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
If Range("N" & i).Value < Date Then Rows(i).Delete 'N is column "Treatment Date"
Next i
Application.ScreenUpdating = True

But I'm having trouble adding this to the code I'm already using (that is working):

VB:

Sub Copy_To_Worksheets_2()
'Note: This macro use the function LastRow and SheetExists
Dim My_Range As Range
Dim FieldNum As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim ws2 As Worksheet

[Code]...

I tried adding it after the code " ' delete columns (after exporting from Current Patients)" but I received the error "Compile error - Duplicate declaration in current scope" as it relates to Dim LR As Long.

I have attached my spreadsheet : Daily Treatment Summary.xlsm

View 1 Replies View Related

Delete Duplicate Rows Containing The Oldest Date And Time Entries?

May 23, 2014

I have a UserForm which writes data to rows in a master spreadsheet. I'm attempting to write some vba code for a CommandButton in the master spreadsheet which can identify and delete duplicate entries based on "user ID", "Date", and "Time". I would like the CommandButton to retain the most recent entry from a user and delete all previous entries.

My master sheet is set out as such...
A, B, C, D,
UserID, Date, Time, Response

The users could potentially submit multiple entries on the same day. Ideally I would like to be able to click a CommandButton and delete each user's submission but retain their most recent one (based on "UserID", then "Date", then "Time").

I've searched all day for a solution and I've come close but I can not figure out a code that accounts for my three variables ("UserID", then "Date", then "Time").

View 5 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

Delete / Hide Rows Meeting User Entered Date

Feb 26, 2008

I would like to setup a macro that brings up a dialog box asking for a date in a specific column. Once that date is entered in the dialog box, the macro would then find all rows before that date (in that specfic column) and delete them.

View 9 Replies View Related

Compare Dates VBA

Jul 16, 2007

Using Excel 2003 on XP.

In Workbook1 I have a list of users with a leave start date in column N and a leave end date in column O.

In Workbook2 some of these of users appear with a payment start date in column C and a payment end date in column D.

I need to find out if the any or all of the payment period for these users in Workbook2 falls within the date ranges of their leave in Workbook1. If not, I need to ignore (or delete) the payment record in Workbook2 for that user.

Is this possible using VBA? Mind you, I'm stuck either way as I simply have no idea about how to approach this (possibly ... would first need to work out how to identify if the dates cross and then perform a lookup ...?)

View 11 Replies View Related

Compare The Dates....

Jan 29, 2010

I have two columns (G&H), one has expected date, and one has actual delivery date. The line amounts vary from 2500 to 8000, I need to calculate if column H is greater than column G for each line, if it is not then that line can be deleted.

View 4 Replies View Related

Compare Dates In VBA ..

Oct 30, 2009

I have the following date formatted strings in two Excel cells "17/12/2009" & "19/12/2013".

Now I try to run the following code on these cells:

If Range("B" & i).Value < Range("B" & j).Value Then
But it does not seem to work, as the dates are compared incorrectly.

Any clue how to bring these dates in a VBA readable format?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved