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.
'for Macro to Delete Duplicate Rows and Retain Unique Value
Dim LR As Long
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)
I am having trouble coming up with an algorithm for deleting columns, based on a certain set of criteria. Heres the issue, all the columns have a "1" the top of column . If that column has a cell number that's greater than or equal to 0.90 or less than or equal to -0.90 then that column gets deleted, if it just has a "1" then the row doesn't get deleted. Very complicated set of criteria.
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.
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.
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
I'm trying to get Excel to delete entire rows based on the conditional formating i'm using.
Basically, I want to get rid of all the rows that are of a certain color (let's say green, InteriorColor = 4). I've already come up with a way to delete rows based on color, but I have to take into account the Conditional formating i'm using.
I run a match formula which returns the row numbers of items i need to delete - at the moment, when a match is found, it will say "Match found, delete row 4" for example. Since multiple matches, potentially into the hundreds could be found - is there a way that i could run a script to delete the rows automatically when a match is found? So instead of putting the above "match found, please delete row 4" into an adjacent cell, could we just delete row 4? & then move onto the next row where a match is found?
I need some assistance with the code below. I have a userform that has a listbox with 2 columns of information. When there is a match between the value in the listbox and the value in the cell for a given row, that row is deleted. I thought this was very straightforward, but i'm not getting this to work right...
Private Sub cmdDelete_Click() Dim Val2 As String Dim Val3 As String Dim doit As Long Dim roww As Long Dim LastRow As Long Val2 = ListBox1.List(ListBox1.ListIndex, 0) Val3 = ListBox1.List(ListBox1.ListIndex, 1) doit = MsgBox("Confirm that you want to delete the entry", vbOKCancel) If doit = vbOK Then
I have a worksheet. Column A contains employee name, column B contains the date of the first day of the week's holidays they are booked for, i.e. 06/12/08 means they start their holiday for one week starting 06/12/08. That's probably really obvious, sorry.
Once a week I update the vacation list. I want to write a VBA code that looks at the date in Column B and deletes the entire row if it has occured. I'd like to use a value in C1 as the reference so if that cell has 06/15/08 in it then the code would delete every row where the date is prior to 06/15/08.
I have a worksheet ( adage inventory ) and I am trying to clean up some un-needed rows.
I need this deleting of rows based on a couple of variables.
I need to keep any row that Col F has the text "QCCONTROL" and I need to keep any row that COL N has the text "HOLD" and I need to keep any row that COL N has the text "REJECTED".
Sub Delete_OK_Lots() lr = Sheets("adage inventory").Cells(Rows.Count, "A").End(xlUp).Row
For x = lr To 2 Step -1 If Sheets("adage inventory").Cells(x, "N") "HOLD" Or Sheets("adage inventory").Cells(x, "N") "REJECTED" Or Sheets("adage inventory").Cells(x, "F") "QCCONTROL" Then Sheets("adage inventory").Rows(x).EntireRow.Delete End If Next x End Sub
I have 11 names, one name in each cell, in one row. I want to be able to delete a row if there are more than 3 names in that row that have the same font colour, eg red. Would have to be a loop type macro to check all of the rows on the one worksheet.
I have a column that has different SIC codes in it (00045, 00046, etc). I would like to set up a macro or filter that would take cells in this column and delete each row that contains a certain code that I don't want.
Example, I want to delete all the rows with SIC codes that have 00345, 00873, 00145, etc in a particular cell. I would like a macro that would look at each cell in the column and delete the rows that I don't want.
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...
I wish to remove the page headers that were imported along with the raw data. Here's the code I've been using.
Column_To_Check = 3 Start_Row = 1 End_Row = ActiveSheet. Cells(Start_Row, Column_To_Check).CurrentRegion.Rows.Count MsgBox End_Row Search_String = "." For Row_Counter = Start_Row To End_Row If ActiveSheet.Cells(Row_Counter, Column_To_Check).Value <> Search_String Then ActiveSheet.Rows(Row_Counter).Delete Row_Counter = Row_Counter - 1 End If Next Row_Counter End Sub
When I import the data from the text file, there are 3 dimensions separated by a period in the same field. I.E: ABC.DEF.GHI and I use the break points to seperate as such: ABC|.|DEF|.|GHI|. Therefore, all rows that contain data also contain a period in cell C, but headers don't.
I have a sheet with 45,000 rows. Let's say each row has 4 columns: Create_timestamp, Update_timestamp, email_address, and o_flag
Many rows have duplicate email addresses. I would like to remove all the duplicate rows, EXCEPT for the row with the most recent Update_timestamp.
And actually, if I could just "hide" all those rows, that would be even better, but I'd be happy just figuring out how to delete all the "old" rows, so I just have a list of unique email addresses, with their create/update timestamps and o_flag column. Seems like this is such a basic use case for "Remove Duplicates,",.
I'm trying to delete a range of rows based on the value of a given cell. For example, if cell AC1 shows 5000, then I'd like to delete (not just clear) the range A5000:AA1048576. Or, if cell AC1 shows 10000, then I'd like to delete range A10000:AA1048576. I've come up with what is below but I don't think this is the right way to do it. I am using Excel 2010.