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
I have some data from A1 to A65536, there are few blank rows in between. i want to delete all the rows below the word "test" in that range. that is the word "Test" appears in A37686 that row and all the below Rows till the last row should be deleted.
few macros/vba that delete rows based on criteria in a cell.
I'm after something a little different. I have a workbook with sheets called Raw Data1, Raw Data2,Raw Data3, Raw Data4 and Raw Data5 and Menu.
There is a column of data in each of the sheets which has a column header of 'Location', however the column where this is located will vary from sheet to sheet. For example in Raw Data1, this is column 'J' and on Raw Data2 it will be column 'M', ( I'm not at work and can't remember the specific column positions for all sheets re: 'Location').
Is it possible to have on the 'Menu' sheet, say in cell B5 a value of 'Locationa' and have rows be deleted in each of the Raw Data that do not match the value in B5?
I have a sheet of data. The data starts in row 4 and can be variable in length. I want to delete rows that do not meet a specific criteria, for example a location, Leeds, which is in column "i" of the table. I have written the below but it seems to delete data from row 1 rather than row 4 and repeat down each row
Code below
Sub deleterows() Dim i As Integer Dim intCounter As Integer Dim strLocation() As String
p = Range(Cells(4, 6), Cells(4, 6).End(xlDown)).count
I have macro code to hide rows based on a certain critera but I don't know how to adapt the code to delete the rows. Basically it checks a column and for every row
how they can be used to delete rows that meet multiple criteria, however all the examples I have found seem to be rather complex and personal to the individuals as opposed to being rather generic. Many people, including myself would find it useful to have a simplified generic answer that can be applied to all kinds of criteria. Such as if the data in column B = x and the data in column C = y delete the row. Here is my example.
SCENARIO EXAMPLE:
In column A - Date e.g. 05/02/2011, 06/02/2011 In column B - Day In column C - Time (in format 09:06:21) In column D – Service numbers dialled (70, 110, 224) two to three digits long
MACRO EXAMPLE:
Delete the row if the criteria in: Column D = 10, 40, 192 or 244 And Column C is between 09:00:00 and 16:00:00
macro code or at least a link to a thread that they think gives a scenario as simple as this.
I was searching for a macro to delete rows that obey a specific criteria. I downloaded [URL]..... and copied the Loop Example to my WorkBook.
When using Mr. de Bruin example, it worked perfectly, it did really delete the rows that obeyed my ">2" criteria. Although, when using on my workbook, the macro deleted the values, not the entire rows. Here's what's happening:
I would like to delete rows, not only values.
Code:
'The code below will delete every row in the usedrange with "ron" in the A column. 'If .Value = "ron" Then .EntireRow.Delete ' 'I use the A column in my example, change the A to your column in this code line. 'With .Cells(Lrow, "A") ' 'Change ron and the A to your search word and column before you try the macro. 'See the examples on the worksheet if you want to do something different. Sub Loop_Example() Dim Firstrow As Long
A macro is required to identify rows within a selection e.g. entire column A, that share the same value, then delete appropriate rows depending on the values in another column. The attached example details the requirements.
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
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.
a VBA problem I have and I do not know how to solve. I use Excel 2003.
I have a workbook with a table; all cells except the table area are locked so at users cannot add any data. The table is on D2: H?. The workbook is password protected (users can only "Select unlocked cells", "Format cells" and "Sort"), and for that reason there is a "Add Rows" macro button so they can add more rows if necessary. It works well.
There is also a "Delete Rows" button, but it does not work as I want it to do. If the table is for example on D2:H4, and I select a cell on row #3 (ex. D3, or E3, or H3) and click on the button it deletes row #3, if I click on the button again it deletes row #4, and if I click on the button again it also deletes row #5 and so on, but it should not do it because all rows below row #4 are locked and should not be deleted.
I found on the Internet a code that I modified, but I do not get it to work. Below are the two macros.
My original macro (it deletes even locked rows):
Sub DeleteRows() ' ActiveSheet.Unprotect Password:="123" ' Selection.EntireRow.Delete ' ActiveSheet.Protect Password:="123", DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowSorting:=True End Sub
The macro found and modified (the original rows are commented):
Sub DelRows_2() Dim rngDelete As Range Do While rngDelete Is Nothing On Error Resume Next
I would like to hide rows for data that does not meet specific criteria. For example: If a user selects room number 101 from a drop down in B1, I want to filter data in range A3:F1000 to show me only room 101 rows (A column) where C column contains a value larger than zero OR D column contains a value larger than zero OR E column contains a value larger than zero. I do not want it to return rows where 101 may be in other columns beside A.
Sample:
A B C D E F __________________________________ 1 ROOM: 101 2 3 4 101 XX 1.2 0 0 P 16 101 YA 0 0 1.1 L 23 101 JJ 3.2 2.1 0 L 55 101 JJ 0 0 1 P
So, if a row contains 101 in column A and all three values in columns C, D, and E equal 0, then those rows will be hidden.
I have a list in Excel, and it has the company in one column, and it's information in the next x rows until there is a blank row (4-7 rows). I get that you can copy the rows under the Company, transpose next to the company, and then delete the contents of the cells that you just took the data from, but I have 6200 rows do to this to, for 500+ companies. I tried recording a macro for two entries, and this is what I have (see below), but how do I modify this so that I don't need to type in every single range, and it will do it to the whole column? The data is in column A, I am posting in column B.
Basics for Macro Requirement:
1. Find the blank cell in row A 2. Skip the next cell/row (this is the company) 3. Select all the cells beneath the company cell, until it reaches the blank cell underneath 4. Copy, transpose these cells next to the Company cell (transpose in column B) 5. Delete the contents in row A that were just transposed 6. Find the next cell with data (company) 7. Repeat Steps 2-6
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).
Aim: To create a Macro to hide all rows where the date in column D is before today. Column D has about 600 rows.
Current solution:
Code: Sub Hide_Old2()
'Worksheet name With Worksheets("Schedule") 'set start of date range Set rngStart = .Range("D2") 'find end of date range
[Code] .....
The problem with this solution is its speed, or lack thereof. It causes the screen to hang and flicker while it cylces through. Is there some way to create a range based on the date and hide the range? or another solution?
Note: Autofilter is not an option, as the spreadsheet with the dates needs to be kept simple for other stakholders and the macro is being run from another sheet.
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
I've already found a TON of threads about this process but nothing that matches specifically what I'm trying to do.
I have a spreadsheet that I'm using to auto fill other tabs with data that only matches specific criteria. Here's what I'm looking to do:
Columns I, J, K, and L may be marked as either Y or N (or blank). I have different sheets that require 1, 2, 3, or 4 of those columns to match Y. For example, on sheet 2 I want to copy the entire row if there's a "Y" match on column I and J. On sheet 3 I want to match "Y" against, I, J, and K. Sheet 4 I need to match only L, etc.
I need the data copied into the existing sheets to start on row 7. I have other data on rows 1-6 that cannot be moved.
delete rows if two conditions are met .THe sample data looks as shown below:
I need to check for a given resource where the allocation hours are highest against which project , and then delete the rest of the rows for that resource records.If the Allocation hours are the same , then the priority column should be considered the highest priority should be included and the rest to be deleted.And if the priority is also the same then need just one record to remain rest of the rows to be deleted.I have priority of business area ranging from
I have the following table, which starts in Column A, row 2. I would like to have a macro, that deletes some rows after certain criteria. If Column F = SO normal and Column E = aa, bb, cc, dd, ee, ff then delete the whole row.
I have an excel 2007 file, which is quite big: ~10mb. There are situated plenty of columns with coordinates. I would like to transcribe it, modify those data, but it will take me ages. I'm not acquainted with macros, but I heard that people can do with them everything using computers' abilities. I'll abridge my whole problem.
I give you a sample of that file, so please open it. In the file you can see two 'columns'. Every column has two other columns called x and y. As you are thinking now these two columns are containing arguments (in x column) and their values (in column y). Every third column is empty. In attached file you see, that arguments are repeating and values are increasing by one. Sometimes there is an argument without repeats, and sometimes there are only two repeating arguments.
What I wanted to do is delete those repeating arguments leaving arguments with the highest and the lowest value. Example: Please look at range A230:B232. There are three repeating arguments and for this case I should delete row 231 from columns A&B and then move cells up to join separated columns. There are also not repeating arguments, which I need to double. If there will be only two repeating arguments I would like leave them. In the end I should have a clear view for all those coordinates.
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.
i have several student ID results that have conditional formatting in place. I'd like to KEEP the following data but delete/hide everything else:
1) Filter out purple rows 2) WITH filtered rows: KEEP any scoreA that's 100% or less than 85% 3) Look at Score A and Score B - KEEP if the difference is greater/less than 5% 4) If a student took testB but didnt take testA, KEEP.
I am looking for a macro to delete rows that DO NOT contain criteria. But what I could achieve so far is deleting all rows except the first word ie ACC here. My code so far is as follows
I have a sheet with say 1000+ rows that is a QA report of possible mistakes found in a employee rostering tool.
The report finds all occurrences where the staff member only has one coffee break rather than the two they are entitled to. However, on days where they have training, or other half day commitments, on of the coffee breaks is not entered into out tool. This means we are getting a whole lot of entries that we do not require(i.e. one coffee break error).
The tool uses icons but the cells do have a single unique character under each icon type. For example, the coffee break cells have a "T" and training cells have a "&" underneath the icon (without quotes). The staff members day is separated into 15 minute blocks and each block occupies a cell in a column. This means each staff members day spans many columns in a row (i.e. C=8am, D=8:15am, E=8:30am etc...).
What I have been trying to accomplish is to create a macro in VB code that will locate any row with both a "T and a "&" and delete it. This will eliminate occurrences that we are aware of and leave only genuine errors. There are other combination's that I would like to include also such as: "T" "[" "T" "#" "T" "@" "T" "]"
The first row is headers and the first two columns contain team names and staff names which I am trying to exclude (because names contains "T"'s ).
I have spent many hours now looking for example code on Google, this forum and other forums, however most of the examples I have found are looking for two criteria within a column or specific criteria that is not suitable to my application (i.e. values <> certain numbers etc...).
I had a raw data sheet in which i need to prepare a statement just like the attached worksheet.
My requirement is to delete all those rows in the department column except the department which starts with "C" Just like "CNN" & "CNN-IN".
Rows with data containing the words starting with "CNN" should not be deleted .The rows can contain words with "CNN" or "CNN*"(here * denotes anything after the word CNN)
I had just formatted the whole worksheet for easy reference.Actually the raw data is extracted from other program which is very clumsy & irregular.
