Deleting Data Based On Dates Using Macro

Jun 17, 2014

Where is that I have 2 sheets of data, sheet1 contains an ever expanding list of data and I'm trying to create a macro that deletes multiple rows of data from this sheet if the date held within column K is less than the date defined in sheet2.

View 4 Replies


ADVERTISEMENT

Deleting Selected Data Based On Cell Value?

Oct 23, 2013

I have attached a sample file.

There are two sheets Stock and BrancnID The stock sheet has a very huge date ( approx 30000 ). The Stock Id of all the records are unique. Every day I get the Stock ID numbers only in excel format from different branches. Every day I have to match the ID numbers received and delete its data in the Stock sheet ( to get an idea I have shown this in sheet3 ).

Is there is any code where I copy paste the ID numbers from the varoius excel sheets into my excel file in the BranchID sheet and the data gets deleted. i.e as I paste the data the record gets deleted but not the Stock ID number. Also the next day when I get new ID numbers and I delete the old ID numbers from the BranchID sheet the data should not re-appear in the Stock sheet.

View 4 Replies View Related

Deleting Rows Based On Columns Data

Jul 2, 2007

I'm trying to write a macro that will look at a table of data, and if column J is blank, it will delete the entire row and shift the data up. Also, if there is a "C" in column C, it will delete that entire row as well. I tried the code below, but for some reason it is not deleting all of the rows that it should. Is there some kind of problem with the code that I'm not catching? It looks like it should work perfectly...

View 9 Replies View Related

Macro For Deleting Whole Row Even If Single Data Is Not Available

Apr 2, 2014

I want a macro which can detect empty cell in rows and can delete whole row in which that empty cell is .and want to shift rows up. Macro should start searching empty cells from Column B meaning that It should not delete cells in Column A.

And I want this function in all sheets except parent sheet at position 1.

File is attached : inquiry 3.xlsx‎

View 6 Replies View Related

VBA Macro To Insert New Row/s Based On Two Dates

May 20, 2014

I need to insert blank row/s between the missing dates and fill the missing date & corresponding employees name. Let me explain in detail,

I have a start date in Cell E1 and end date in Cell G1. Need a macro to verify the date range in Col A with these start & end dates and insert blank row for missing date corresponding to employee names in Col B. This should be repeat till last used row of the sheet. Attached sample(Before&After) workbook.

Both missing date and respective names to be filled in the newly inserted rows.

View 2 Replies View Related

Deleting Rows With Certain Dates

Apr 18, 2008

I'm trying to create a macro that removes rows that contain dates past todays date.

E.g, in columns A and B are people's names and in column C are start dates. I need to remove any rows of data that contain a date in column C past todays date.

Here is what I have put together so far, but it doesn't seem to work:-

Private Sub CommandButton1_Click()
Dim LastRow As Long
Dim c As Long

LastRow = Range("c65536").End(xlUp).Row
For c = LastRow To 1 Step -1
If Abs(Cells(c, 9)) > Date Then
Cells(c, 9).EntireRow.Delete
End If
Next
End Sub

View 9 Replies View Related

VBA Macro To Insert Blank Row Based On Two Dates

May 16, 2014

I need to insert blank row/s between the missing dates. Let me explain in detail,

I have a start date in Cell B1 and end date in Cell D1. Need a macro to verify the date range in Col B with these start & end dates and insert blank row for missing date corresponding to employee names in Col A. This should be repeat till last used row of the sheet.

Attached sample (Before&After) workbook.

Insert Missing Date.xlsx‎

View 4 Replies View Related

Deleting Data In A Range Of Cells Based On If There Is Data In Cell Outside Range

Aug 1, 2013

I am attempting to make a range of cells' data be deleted if a cell outside of that range that had a name in it is no longer there. So if I have a list of names in cells A1:A6 and a range of data in cells D10:E20 that corresponds to a name in cell A2. If the name in A2 is deleted I would want the data in D10:E20 to be deleted.

View 9 Replies View Related

Macro To Return Yes Or No Based On Multiple Cell Dates

Dec 16, 2008

I need a macro that based on multiple cell dates determines if the person is qualified or not, and puts yes or no in column A... all of the dates are yearly.

View 5 Replies View Related

Macro To Select Dates Based On Cell Values

Feb 20, 2009

I might be using something completely wrong here, but can anyone tell me if this code is possible or am I being very naive.

H4 and I4 are cells in which I want to enter dates, and then I want these dates to be used in a custom filter on another page.

Below is what my limited understanding of VB came up with.

Sub Date_Range()

Dim First As Date
Dim Last As Date
First = Range("H4").Value
Last = Range("I4").Value
Sheets("Graph Data").Select
Selection.AutoFilter Field:=1, Criteria1:=">=First", Operator:=xlAnd _
, Criteria2:="

View 9 Replies View Related

Extracting Data Based On Dates

Aug 5, 2009

I have a table with 'Product Rereference', 'Date', and 'Estimated Assets' Columns. I need to run a formula that will extract the number in my 'Estimated Assets' column, based on speicific list of 'Product Reference' numbers that I have filetered, and a specific date - 1/31/ of each year.

I've been trying to create a formula, however I can't seem to work it out.

I have attached a file, which should make things a lot clearer :P I am trying to make the data extract into the table to the right. I have filled in the first few columns manually, to show what I would like to formula to achieve.

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

Copying Data Based On Dates And Ages

Feb 19, 2014

I am finally at the end of this project. I need to find a code that will copy any lines that the person has turned 65 in the previous month or that will turn 65 in the current month to the Monthly tab.

Sample Macro jscalem.xlsx

View 3 Replies View Related

Copy Data From One Sheet To Another, Based Upon Dates

Jul 14, 2008

Copy Data From One Sheet To Another, Based Upon Dates ...

View 9 Replies View Related

Macro To Run Autofilter To Pick Out Data Between Two Dates

Apr 30, 2014

I am having trouble with the following macro,

[Code] .....

When I press F8 and step through the macro, the StartDate and EndDate both seem to populate correctly.

The macro seems to go through the motions but returns NO results, unlike the recorded macro.

Macro code to "Clear Filter".

View 2 Replies View Related

Macro Which Picks Out Dates And The Data Relating To Them

Oct 29, 2008

At the moment all our work is logged by codes eg. A1Misc, B5Change, N9Important. As well as the codes, we enter the date in the column next to the code along with another Column to show the amount.

I need a Macro to look at the date column and only take dates which have Sept then go to the column next to it and pick up the codes individually and sum up all the amounts for the codes.

I have attached an example which shows from A13 downwards what I need the macro to show when finished.

View 3 Replies View Related

Add To Dates Based On Criteria & Flag Overdue Dates

Dec 31, 2006

I have a very large Excel spreadsheet that is generated through an Oracle application at least once a month at work. I would like some code (either a worksheet function or a VBA) to check and report the following. If the frequency in column C is "Monthly" or "Weekly" just go to the next row. If the frequency in column C is "Annual", add 183 days to the date in column B; if it is "Semi-Annual", add 92 days to date in column B; if it is "Quarterly" , add 46 days to the date in column B; if the frequency in column C is "2-Year", add 365 days to the date in column B. Once the check has been made; I need the new total or date that was calculated checked against the date the report is being run (system clock date). If the date or total days is less than today, a new worksheet, titled "Late" needs to contain that row of data. If the date or total days is greater than today's date, just go on to the next row. I've attached an example worksheet.

View 9 Replies View Related

Deleting Rows Based On Their Value

Jun 26, 2008

I am having trouble with the .SpecialCells(xlCellTypeBlanks)

What I need to do is for a macro to first go through a range ("G8:G50"), Add a 0 value to any blank cell.

Then I want the macro to delete the entire row for any of the cells that have a value of 0 in that range of "G8:G50"

View 9 Replies View Related

Grouping Dates In Pivot Data By Month As Well As Sorting Dates In Ascending Order

Apr 30, 2013

I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.

Pivot Table  ABC3Row LabelsSum of DebitSum of Credit
413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012
18898.0318065.4813/10/2012 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26

View 3 Replies View Related

Deleting Rows Based On Two Criteria?

Jan 30, 2013

I have a column with dates (dd.mm.yy) and I have a column with names. Moreover, several dates and names are repeated. What is needed, is to delete all the rows in which the difference between dates is smaller than 1825 days (5years) for the same name. (Namely, if I have three rows 01.01.1996 - "A"; 01.01.2002 - "A" ; 01.01.2005 - "A" I want all the rows with "A" to be deleted)

View 5 Replies View Related

Deleting Rows Based On Date

Feb 24, 2014

I run a daily report that contains material usage and I copy that into a workbook designed for Data Analysis. I only keep a years worth of usage in the workbook so I need part of my macro to look at todays date and delete anything that is older than a year.

View 4 Replies View Related

Deleting Rows Based On Value Of Cell Within That Row

Nov 14, 2008

I would like to delete rows in a worksheet based on a value of a cell within that row. The worksheet will not always have the same number of rows. I will be looking at the value of each cell in column "AD", which is a date, and if it is not equal to 00/00/0000 then I want to delete that row.

View 7 Replies View Related

Deleting Rows Based On Value In Columns

Apr 28, 2009

I'm sure this is simple code, but...

I have a data table with over 20,000 rows that refreshes each day. I need to run a bit of code when it refreshes that says if the value in column R is 2, then delete that row.

View 14 Replies View Related

Deleting Rows Based On 2 Columns

Oct 9, 2011

I need to delete rows that are blank, 0, or contain errors (#REF, #N/A, etc). My problem is that once in a while, an error will show up in a singe cell of a row that I need to keep so a simple deletion of only errors/blanks/0 will not work for me.

The two cells I need to check for errors, blanks, and 0's are D and E. There can be an #N/A in col D and a 0 in col E or both D and E can have errors. Blanks will occur in all cells of the row.

I have tried to use the code below but it doesn't do anything, but it also doesn't give me an error.

Code:
Sub DeleteAll()
Windows("Template.xls").Activate
Dim r As Long
For r = 2 To 36500
If Range("D" & r).Value = "#N/A" And Range("E" & r).Value = "#N/A" Then
Range("D" & r).EntireRow.Delete = True

[code].....

View 1 Replies View Related

Deleting Rows Based On Set Of Conditions?

Dec 30, 2011

I have the following data

Ticket NumberOriginSectorDescFB DataPax NameClassPointO CCRegionDestin
976533060397MAADXB-DFWCDHJKL3SD RAJESHFirstDFW---
976533060397MAALIT-DFWCDHJKL3SD RAJESHFirstDFW---
976533060397MAADFW-DXBCDHJKL3SD RAJESHFirstDXBUS--
976533060397MAADFW-LITCDHJKL3SD RAJESHFirstLITUS--
976533060397MAADXB-NBOCDHJKL3SD RAJESHFirstNBO--NBO
976533060397MAANBO-DXBCDHJKL3SD RAJESHFirstDXB---
976533060398MAADXB-DFWCDHJKL3SD RAJESHFirstDFW---
976533060398MAALIT-DFWCDHJKL3SD RAJESHFirstDFW---
976533060398MAADFW-DXBCDHJKL3SD RAJESHFirstDXBUS--
976533060398MAADFW-LITCDHJKL3SD RAJESHFirstLITUS--
976533060398MAADXB-NBOCDHJKL3SD RAJESHFirstNBO--NBO
976533060398MAANBO-DXBCDHJKL3SD RAJESHFirstDXB---

I need to delete rows based on following conditions, If,

Ticket number, origin, FB Data, Desc, pax name is same i need to delete all rows except the Row which has Destin filled in. In the above example I need rows only with NBO

Result should be as follows,

Ticket NumberOriginSectorDescFB DataPax NameClassPointO CCRegionDestin
976533060397MAADXB-NBOCDHJKL3SD RAJESHFirstNBO--NBO
976533060398MAADXB-NBOCDHJKL3SD RAJESHFirstNBO--NBO

I need one more criteria to be tested the above result. IF,

origin, FB Data, Desc, pax name is same and ticket no is A1+1 i need to delete the second row

Final result should be

Ticket NumberOriginSectorDescFB DataPax NameClassPointO CCRegionDestin
976533060397MAADXB-NBOCDHJKL3SD RAJESHFirstNBO--NBO

View 2 Replies View Related

Deleting Rows Based On Cell

Apr 19, 2012

This is the final code needed...

Col G = Product #
Col H = Description

if Col H ( description ) has 'CVP' or "CO2" or 'CKN' = delete row

unless....

Col G is 617367, 858556, or 882949 then keep that row..

View 7 Replies View Related

Deleting Rows Based On Criteria

May 29, 2012

I have this excel file where I every day have over 10.000 rows. I have 2 sheets, one called "Data" and once called "Include list".

The Data sheet contains a list of all of our customers and their customer IDs. The Include list sheet should contain the Customer ids which I want to keep in the Data sheet.

So what I have done is to loop through the Data sheet. If you are in the Include list sheet you should not be deleted fromt he Data sheet. If you are not then the row should be deleted.

I have actually done this and it works but the problem is it take a lot of time to run. I tested in earlier today and I had to break it after 20 minutes which is way too long for our users to wait.

This is the code I wrote:

Sub Include()
Dim FindString As String
Dim Rng As Range
Dim RowNr As String
Dim Lookup_x As String

Application.ScreenUpdating = False

[Code] ........

So my question is, is there any other way to speed this process up? The ScreenUpdating part I have tried but it didnt really speed it up as much.

View 1 Replies View Related

Deleting Rows Based On A Condition

Aug 8, 2012

I have a data dump. I download everyday with 200 rows and 20 columns. I need to remove any rows where there is no value in either columns C or D. Is there a quick way to do this?

View 7 Replies View Related

Deleting Rows Based On Two Criteria?

Apr 20, 2013

I have a very large spreadsheet (>10,000 rows) of data. I did Conditional Formatting based on duplicate values for the serial numbers column (B) and then sorted to "Put Selected Cell Color on top". Next I did a sort by the Last Scan Date column M (Oldest to New). The date/time format appears as follows

I used the following macro to delete rows with duplicate serial numbers but retain the row that has the newest time stamp. When I run my macro it's doing the opposite where it deletes rows with the newest time stamp and retains the oldest time stamp.

Code:

Sub Test()
'for Macro to Delete Duplicate Rows and Retain Unique Value
Dim LR As Long

[Code].....

View 9 Replies View Related

Deleting Sheets Based On A Range

Nov 13, 2013

I'm pretty new to coding from scratch in VB, and I've got some code that should loop, but it doesn't. The idea eventually will be to download a spreadsheet, make a new sheet for every item in a range, and then filter for each of those items and put it in the right sheet. I have to code to create the sheets (lightly modified from something I got here)

Sub CreateSheets()
'Written by Barrie Davidson
For Each c In Sheets("FilterList").Range("b2:b74")
Sheets.Add
ActiveSheet.Name = Right(c.Value, 30)
Next c
End Sub

That's working well, so I've tried to modify it so that it will also delete sheets based on the same range. I've gotten it to delete the first item in the range, but then it stops.

For Each c In Sheets("FilterList").Range("b2:b74")
Application.DisplayAlerts = False
Worksheets(c.Value).Delete
ActiveSheet.Name = Right(c.Value, 30)
Next c
End Sub

By the way, I don't know what the ActiveSheet.Name = Right(c.Value, 30) line does, but I put it in since it was in the code I was modifying.

View 9 Replies View Related







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