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.
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?
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.
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.
1. Remove J,K,N,A Columns,
2. In the last O (TIMESTAMP) column, the date is 14-Jul-09 format change it to 07/14/2009 (this format mm/dd/yyy
3.Filter L column (VAL_INLAKH) Remove all rows from whole sheet which has 0 value
4. Column C (EXPIRY_DT) date format is 24-Sep-09 , "dd-Sep-09" change to "Sep" only
5.Merge Column B,C,D,E (SYMBOL.EXPIRY_DT.STRIKE_PR.OPTION_TYP
respectively )
I am trying to do is to get rid of "/2010" and just have a fraction such as 4/5 rather than 4/5/2010. I tried find and replace and that doesn't work and I also try converting the format for the 500 cells to dd/mm but in the Fx formula box I still get 4/5/2010 rather than just 4/5. Also I should mention that I tried past special-values when I converted the data to dd/mm and I still get the dd/mm/yy.
Currently, I am working on a macro to see if I can just simply delete "/2010" and replace it with nothing.
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.
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies View RelatedI use the following macro to delete the columns with duplicate dates in it. It works ok but when a date is duplicate more than once – say 4 time – it only delete 2 of the 4. I want it to delete all duplicates (entire column) and only leaf the last one to the right.
Sub DeleteExtraDates()
Dim c
For Each c In Selection
If c = c. Offset(0, 1) Then
c.EntireColumn.Delete
End If
Next c
End Sub
removing duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
In my excel I'm copying rows upon specific criteria to another tab.
The question is how I can delete copied rows and update the original tab without empty rows? (N of rows is always changing)
Basically I have a list of employees. This list is simple in that it shows their off time "From:", "Till:". in columns to the left of there name. So in Cell A:A would list all of there names.. In the example sheet I have just a few listed. To the left of their name if "From:" (column B) and then "Till:" (Column C). And this From, Till repeats itself for several columns so that multiple off-times may be made for each employee.
Basically what I want to do is delete any sets of dates that are expired (based on the "Till:" date) and shift the remaining dates over. So in other words, if Sam has a date in there of 8/1/09 to 8/1/09 meaning he is going to be off for just that day, and today's date is 8/2/09, then that "from" and "till" date of 8/1/09 to 8/1/09 would get deleted from his list and the remaining dates (if any) would get shifted to the left.
Hopefully I have provided a good enough explanation of what I am trying to do here.. Please let me know if you have any questions! I have provided an example the reflects my setup (and the setup can be changed if it makes anything easier)
Is there a limit on the number of rows and columns that can be deleted in a macro on Excel 2003? I am trying to create a macro that, amoung other things, delets 1119 rows and 54 columns. If I delete the columns first, the rows will not delete. If I delete the columns first, the rows will not delete.
View 12 Replies View RelatedI am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another.
My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows.
So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
[Code]....
My problem is I don't think I have approached this the right way but am unsure of where to go with it. The code as is does sort of work but I still get some duplicate and zero lines in my results.
I need a macro that can look at multiple (say three) rows and delete those rows if they match another three rows in the worksheet.
For example:
1 0
3 1
5 7
4 4
6 5
8 3
1 8
5 2
3 9
6 5
8 3
1 8
7 5
If three rows are the same, then one set is deleted (it can be either bottom or top set). I would like to do this for rows 1-500 in the worksheet.
I have the following codes to delete all blank rows in column A
Dim lastrow As Long
lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
MsgBox lastrow
With Sheet1
For t = 1 To lastrow
If Cells(t, 1) = "" Then
Rows(t).Delete
End If
Next t
End With
End Sub
Although it is working , it is not deleting all the blank rows at once, I have to keep pressing on the macro button running the macro several times, until all blank rows are completely deleted.
I have a worksheet in which I have sorted the data based in date and numbering (column I and E). I would like to create 2 macros for following actions:
1- all rows with the value "TOM" in column C will have to be deleted.
2- all rows with a value of 601 or 602 in column E, will have to be moved to the bottom of the sheet after the last row with data. The rows that have been moved will have to be sorted based in date (column I) and numbering (column E).
I am working with timesheet data (name, project code, task code, date, hours etc...) in one spreadsheet and rate card data (name, role, day rate etc...) in another. My task is to pull together some of the information in each of these two source spreadsheets and compile a report. This I have done no problem. However, where a person works on a particular project and task on the same day and records multiple entries (which could be negative) I need to consolidate the hours in all these matching rows and have just one row reflect the total hours worked and delete the other duplicate rows. So an example would be:
Project | Task | Name | Role | Date | Hours
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | -2.5
123456 | 1.001 | Paul Jones | Project Manager | 20/02/2008 | 3.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | 7.5
123456 | 1.001 | Jo Brown | Developer | 20/02/2008 | -7.5
123456 | 1.001 | Sam Smith | Architect | 20/02/2008 | 7.5
Should be processed and come out like this:.......................
I have an imported report in a spreadsheet. It imports to three columns. I need to check each row in column A for three seperate criteria and delete the rows I don't need. I need to delete blank rows and check next row for page header info. Delete these and next rows to next blank cell. Check next row for page header and not delete if not page header. Several rows down will be a cell with 23 blank spaces before the word Reg: and sometimes other words past this but always this first. This row is to be kept. I looked at the FAQ's example of Deleting but I don't think it will work. I also need to put a key word in column A at a point where I want to stop. This report is a couple thousand rows long so a VBA procedure would really save time. I have a procedure I use to check for two zero's in two cells that hide these rows but I couldn't modify it to work on this report.
View 9 Replies View RelatedNeed to solve my problem in the thread "Type Mismatch Error Message". Now a new problem has come up in the same code, so - according to the rules - I've started a new thread. (This one is most likely due to my poor knowledge of VBA syntax).
Sub Delete_invalid_rows()
Dim i%, j%
Dim Nr%, valid As Boolean, BYPdata As Boolean
Dim ar1 As Variant
Dim ar2 As Variant
Dim ar3 As Variant
Dim ar4 As Variant
Nr = 20
ar1 = Array(11, 14, 19, _
20, 22, 25, 26, 27, 28, 29, _
30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, _ .................
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
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 RelatedI am using Excel 2010 and I need a macro to delete all text from cells and leave the dates.
Below is an example of the contents of six cells. Some have just text, but some have text and dates.
1st Time Attender: 4/8/20121st Time Attender:1st Time Attender: 4/5/20122nd Time Attender:2nd Time Attender: 4/8/20122nd Time Attender: 4/5/2012
In this example, I would want the new cell contents to be:
4/8/2012
4/5/2012
4/8/2012
4/5/2012
I would like to write a macro button, in order to lookup through col. "D" and where find same invoice number (invoice number combine with 3 letters plus dash), should sum the corresponding rows' amounts in col."J" into one row and the other row should be deleted.
View 3 Replies View RelatedI have a sheet as shown in the picture below. If the cells in Column N has some values that are the result of some calculations. How ever if there are more than one of it, the entire row has to be deleted.
Eg:The value in Column N 816.323 has 3 rows below each other.Only one has to be there.There can be upto 10 values one after other in this way.
I tried to write a progrma by using loops and cehcking if ther is value in the cell,go to cell below and if there is value,delete the row in a loop of 10 times.But it didnt work
I have a workbook where I import data from another workbook. This might get done several times a month. As I go through the imported data I delete rows not needed which gets place into another worksheet. What I need is when I import data again is a macro that the worksheet where my already deleted rows are and compairs them to the new data imported and if the first 13 columns of data matches it delest them off the imported sheet.
I found some code which I altered at [url]
and posted the same question but haven't got any response as of yet.
So the code that i go works but only if the row matches in the exact order on both sheets so e.g. (if row 1 on sheet1 matches row 1 on sheet2) it works but if (row1 on sheet1 matches row 3 on sheet2) it does not.
Here's the code I have so far.
Public Sub delOLD()
Dim i As Long
Dim iLastRow As Long
Dim ws As Worksheet, ws1 As Worksheet
Set ws = Sheets("Data") ' Imported Data
Set ws1 = Sheets("Old Records") ' Deleted Data
'ws1.Visible = xlSheetVisible
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
Worksheet is an action log. It has a column containing "due dates". I want to count all rows (dates) that are "less than" today (to see what work is overdue). I also want to count all rows (dates) that will come due in a week. Conditional formatting highlights them OK but I also want to maintain a set of counts. I can do all this in a macro but I don't want to use macros, just formula.
View 2 Replies View Related70 rows, Col A is a start date, Col B is an end date
What I want in Col B, row 71 is the average elapsed time between start and end dates...
How can I use a pivot table or advanced filter or macros to view rows by
Only today’s date (1 row) ?Only this week ?Only last week ?Only last month ?Only last 12 months ?All dates ? That is, rows are headed by day. I select the rows that I view.