I have a table of data with which I need a macro to clear a range of data based on certain criteria. I have attached a file as an example. In this example the current period is 6. I therefore need to clear columns which have a period of 7 or higher.
Essentially, if the current period is x, then clear the range in the period columns if the period is greater than x.
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.
Extract Rec1.xlsx
I need a macro to delete old data from a large selection of data, in order to keep the size down.
What I want this macro to do is
Check all rows from 5 downwards.
If A5 (date) is less than cell $B$1 AND B5 is not equal to C5 then delete the whole row.
Continue until reaching the bottom.
I have a list of several ranges defined in vba, and i am trying to find which range a cell containing word is in excel. Im am looking for help for a good way of doing this.
For an example: Say I have 2 ranges defined, the first range corresponds to column A, the second to column B (just an example). The word "Dog" is written in cell B15. I want to find out in vba what range "Dog" is in. I would like some vba code to check and find either if it in a given range (boolean) or a way of checking a list of ranges and returning which it is in. The word will only appear once on the spreadsheet. I have played around with the find command (A=range.find(Pl), then check if A exists) but i havent had any luck (I get an 'Object variable or With block variable not set) I tried searching for a while, but was having trouble finding something related to what i am trying to do.
Formula below works for one string, "review" but i also need it to work for "audit" "project" and "done" too.... so essentially someone can go into the sheet - hit a button and remove all these lines in one go.
[Code] ......
I would like to obtain the below issue using VBA. I would like to delete the row if the criteria hasn't met. For example,
If cell "B3 = Dec-12" and if cell C3 is not equal to "31" then delete the entire row.
If cell "B4 = Feb-12" and if cell C4 is not equal to 29 then delete the entire row.
If cell "B5 = Feb-13" and if cell C5 is not equal to 28 then delete the entire row.
This need to be applied for the last row in column A.
Basically I am looking if column B has month and Year (i.e. MMM-YY) and if column C doesn't have that particular month days then the entire row has to be deleted.
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...).
if in column A in some row is text "today" then delete this row and delete all rows until in row B wont be empty cell. I have start (If in A is "Today" then ... ) but thas's all
View 5 Replies View RelatedI 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.
The department column might be in any column.
I am attempting to create a macro that will delete an entire row, based upon multiple criteria. The data looks something like this:
-----Column A-----------Column B---
1.-- Computer_X ------- Software_A
2.-- Computer_X ------- Software_B
3.-- Computer_X ------- None
4.-- Computer_Y ------- None
5.-- Computer_Z ------- Software_A
6.-- Computer_Z ------- None
I would like to delete the entire row if:
1) Column B entry is "None"
AND
2) Column B has entries other than "None" that match Column A
So I do not want to delete a row with a unique name in Column A and "None" in Column B. I only want to delete the rows that have "None" in Column B, and multiple entries for the match in Column A.
Using the data example above, Row 3 and 6 would be deleted. Row 4 would not be deleted, since Column A (Computer_Y) had only one row of data, with "None" entered in Column B.
I want to be able to delete certain rows based on multiple criteria. If data in the row does not meat the crieteira, it shud be deleted. I have attached the excel file sample in which I want to keep the highlighed rows and detele the rest. Its base on Origin city and Destination city combination. Eg: If Origin city="A" and Destination City = "B", I want to keep the row. Like wise for all highlighted rows. but if the Origin City = "B" and Destination City = "U" I want to delete the row.
View 9 Replies View RelatedIs there a vba code that can delete the entire row based on two criterias? If a row has a location of Canada and is a female, then delete the entire row.
Name Location Gender
Alan Mexico Male
Dick USA Male
Sharon Canada Female <-- This will be deleted if based on criteria
Mike Canada Male
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.
What i really need it to do is if each row in column H = "Cleared" then to only clear columns A, C to H. Theres a formula in B that needs to be left... any ideas?
Also i need this to work on all atbs as the commandbutton will on a master tab
VB:
Private Sub CommandButton1_Click()
With ActiveSheet
.AutoFilterMode = False
[Code]....
I am trying to run (or loop) through a column of cells, and if cell contains certain text (e.g. ALPHA, BRAVO, CHARLIE) and delete the row if found.
My macro as I run it:
Sub DATA_TEXT ()
'Number of cells to loop through (I am unsure how the .xlend works!)
For i = 1 to 2500
'If function defining the criteria.
If cells (1, i) = "ALPHA" OR "BRAVO" OR "CHARLIE" Then
[Code] .....
I am trying to figure out how to have some VBA look down Column 1 and where the Cell's string value = "GRANDTOTAL", to DELETE that ROW and ALL ROWS below.
Can anyone help me out in writing the Visual Basic code?
Simple code that can delete entire row if certain criteria is met in a single cell
Example
I have a bank statement where under a first column (DATA TYPE), the cell could contain either "DATA" or "TOTAL"
How can I delete the rows contain the word "TOTAL" assuming the column is already sorted.
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 2 columns A and B. The data is in column B.
What I'm trying to do is delete entire rows from my current selection if they do not begin with mailto:
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
[Code] ....
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'm okay with Excel, but I'm just getting into utilizing VBA and I've been searching high and low for a simple VBA code that will remove/delete rows based off a single criteria without loop as there are over 40,000 rows. I tried a couple that I found onilne, and adapting them to my criteria range, but no luck. (All the ones that I found that work use loop and it takes about 15 mins to run through the entire spreadsheet)
I would like to maintain my first row as it's my headers. My single criteria is to remove all rows that have "NO" in column D.
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....
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)
I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}
I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message
Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)
Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)
-Didnt get thru this bit to start building on the Function! keep giving me #Value!
Macro for AverageIFS, with multiple criteria in the same criteria range....
View 9 Replies View RelatedEach 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
[Code]....
I would like to be able to delete the rows, starting from Employee 2 to just before the second SUB TOTAL.
So, all I would have left in column C is
SUB TOTAL
SPACE
I have some VB code, courtesy of OzGrid and Davc4, that works well to delete duplicate rows based on criteria in Column A of the active worksheet (albeit a bit slow on large files).
How do I modify the code below to evaluate duplicate data in Columns A through D? .....
I'm trying to use VBA code to delete certain rows based on a couple of criteria as follows:
1) If column header is "TERMINAL NAME" and the cell value in that column is "BONDDESK", I need the entire row deleted.
2) Similiarly, if the column header is "PRODUCT TYPE" and the cell value in that column is "CORP", that entire row needs deleted as well.
The information is coming from another source, so the columns may change from day to day, i.e, "TERMINAL NAME" may be in Column L one day but in Column O another day.....
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
View 9 Replies View Relatedhow 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
[Code]....