I have a Macro that I use daily on a spreadsheet I get with raw line-item transaction data on it. Basically, it deletes columns I don't need to look at, applies column headers, deletes certain rows that I don't need to look at, applies an auto-filter and filters to the first group of data I need to look at, and puts a subtotal on a couple of columns at the top so I can see totals on filtered results.
I need an additional function in this Macro. At some point in the middle of the actions above, I need to evaluate all rows and delete those that are 'opposites' based on a couple of criteria. Basically, I need to delete any transactions that have been refunded in their entirety, because for my purposes those are zeroed out and I don't want to see them....
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 a spreadsheet that is updated daily with data that falls into one of two categories: a) completely new to the sheet or b) existing from the day before but not taken care of yet. The items which fall into category a are evaluated, and notes added to the sheet. The items in category b must be compared to the new data, and duplicated lines deleted but notes from previous evaluation must be kept on the sheet. In addition, anything on the sheet that is not included in the new information needs to be deleted. I've set up a sheet with all old and new data with dates the data showed up as well as the notes associated with the old data, and have concatenated all the info from each row into column V (from another suggestion I got on a similar but not exactly the same problem which I also still haven't figured out). The sheet has been sorted by col V so any matching rows should be within 3 or 4 rows of the original. The problem is that my code does absolutely zilch!
Sub DeleteRepeatedRows() ' goes through coord. sheet and identifies duplicated lines, copies the date from the old row ' copies it into the new line, then deletes the old line Dim rCell As Range For Each rCell In Selection If rCell.Value = rCell.Offset(1, 0).Value Then rCell.EntireRow.Delete End If If rCell.Value = rCell.Offset(2, 0).Value Then rCell.EntireRow.Delete End If.........................................
Imagine column A sorted by invoice numbers. Column B has payments applied but due to movement between unapplied cash, some numbers appear as negatives and positives for the same invoice number.
I want to have a formula which says that for each invoice number, look in column b and eliminate opposite numbers and post only unique values. Keep in mind that there may be multiple lines with the same invoice number.
For example, starting with line 5, for invoice 123, payments 400 and -400 in line 5 and 17 and payments 315 and -315 in lines 7 and 9 will zero out but any other unique payment will be picked up and displayed in column C. Lines 5 and/or 17 will be blank in column C.
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 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 am a pharmacist that runs reports and I need to sort and exclude data
It is in Excel 2003 format
I run a report that includes a patients name in one column, the medications name in another column and if the medication was withdrawn, wasted, returned or restocked in another column. Withdrawn, wasted, returned or restocked are treated as different functions and each have their own row entry. Patient and medication name are the same in all situations
I need a macro that will look at the patients name and medication name, see if it is the same and delete all rows that have a withdrawal with an associated wasted, returned or restocked.
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.
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
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 am trying to use the code below to eliminate data in a spreadsheet based on certain criteria. I have been able to get this to work with text fields just fine, but now I am running into an issue with numbers. I am trying to get this code to delete rows if they have the word "unknown" in column C and if the value in column B is greater than 900 hours (999:00:00), the numbers in this column are formatted as [hhh]:mm:ss. What should I tweak to make this work?
I have an extremely large CSV file that I am opening via Excel. Can anyone tell me how to delete a row using multiple criteria. For Example, i want to delete the whole row if a certain cell doesnt equal GA SC or NC. I have seen how it can be done using one criteria but not multiple ones.
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?
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 am looking for an Excel VBA code that can delete pair of rows that has certain criteria. I have included an example in the attachment and highlighted the rows that should be deleted. Since the values in each coloumn is going to change every day , I am looking for a dynamic code.
The objective is to:
To delete rows that has same code (columnd D), same basis (coloumn E), same Effective (coloumn H), value of TT (Column J) is either TI or TO and has offsetting Amounts (L) ie the sume becomes zero. Example pair of row 7 and row 8 as well as pair of row 12 and row 13 should be deleted.
TO and TI are actually transfer in and transfer out. Since the TI and TO for these rows make the amount zero for same code and same basis on the same effective date, I do not want to include this in the spreadsheet.
I want to delete the rows if they do not equal "TL" based on certain criteria, but save the info then delete the rows if they do not equal "Fedex" based on certain criteria, but save the info then delete the rows if they do not equal "LTL" based on certain criteria
After the information has been filtered it is supposed to load to a template giving monthly information for each of the above, but this is not working
VB: If Load = "LTL" Then Range("A2").Select Selection.Sort key1:=Range("F1"), order1:=xlDescending, Header:=xlYes For x = 2 To TotalRows If Cells(x, 6).Value > 10000 And Cells(x, 1).Value = "FEDX" Then
I am pretty sure that my problem lies within the parenthesis where it says Rows("2:" & x-1).Delete. Am I supposed to put something else there since I am deleting rows based on three different loads?
I need create a macro to perform the following - I don't know if it needs to be an Excel macro or via VB. This is to speed a process up and eliminate any spreadsheet messing around for our "admin" staff - they are not good with excel at all!
So, I have a daily/weekly down load of a spreadsheet (exported from access).
* I want to copy the values of column AK to A * Then delete all entries in column D that equal 19, 20 and 6 * Then delete all entries in column AL that equal "A","B","C" or "D" but not "E","F" or "G" * Then delete entire rows where the value in column A is a duplicate within the list
I will call this spreadsheet, for the purpose of this exercise, the "Master". I now have another spreadsheet with values in that should relate to values in either (or both) columns A and B in the "Master". I need Spreadsheet 2 values to be highlighted as cross checked in the "Master" and then all those in the "Master" that do not appear in the 2nd S/S will be normal white cells thus showing that they need to be chased by our Admin staff.
Each week I run a report that produces over 2,000 rows. I have to manually go through and delete things, by the time I am done, there may only be 320 rows left. I would apply a recorded macro, but the report is not consistent each week. There may be more or fewer rows depending on the week.
I would like to be able to run a macro that looks in column B for a specific value and if it contains that specific value, I would like the cells to the right of it to have their contents cleared.
Next, in column C, I have...
Employee2's Name Employee2's Number SPACE
I would like to be able to delete the rows, starting from Employee 2 to just before the second SUB TOTAL.
There is no where else for me to go. My problem is: I have a table with over 30,000 rows and columns A - W. The first column A contains UNIQUE ID. Column G contains CATEGORY CODE.
Since Unique ID (column A) can be listed more than ones, I need to select only those records (rows) that correspond to the following: Category Code (column G) is either 14, 15, 16, 17, 18 and not any other. I hope you can see the attachment, UNIQUE ID = a;does not satisfy my criteria as it does contain Category Code 14, it also contain other codes. The final result from the sample provided would be extracting data for UNIQUE ID = e (since it is the only record that does not contain any values other than 14, 15, 16, 17, 18).
I am trying to loop through a column and cut and copy cells containing one of ten or more criteria (text strings) to the next worksheet, which already exists.
The basic loop is set up, and I understand that an array for the multiple criteria is needed but am falling at this hurdle, as well as struggling with the routine to copy the row to the next available row on the next worksheet.
I have omitted to post my effort so far, as it's embarrassingly basic, but can anyone help with the code?
Example attached. I need to filter rows based on a start date and stop date, columns C and D. So for example the filter date is 01Mar07 (located in A5). As this date in this cell is changed the rows are filtered accordingly. I need to filter rows so that any row with a start date which includes Mar 07 is shown and I need to include all rows that have an end date in Mar 07. This would result in the inclusion of an event that started in Feb and Ends in march being displayed. Additionally, I would need to clear the filter. I'm just starting out, I'm sure this is easy for you all the excel experts., and you may probably have a better method to approach this.