I've got a menu in which the user has a checkbox with 3 choices.
Choice 1 = Red
Choice 2 = Blue
Choice 3 = Green
My macro looks at what the user selected and then goes through a column on the spreadsheet - for example say the user only checked Green. In this case, a variable would be set to 001 (if they checked blue and green the variable would be 011...). The then macro goes through coulmn x and whenever there is a "Green" in column x, it selects the row, deletes it, and evaluates column x's next row.
My problem is, that I'm not deleting all of the rows with Green in column x. I've set breaks throughtout the code and the variables are all set correctly, I've checked for spaces before/after "Green" in the rows not being deleted, and nothing seems to work.
Here's my code:
Case Is = "001"
Do Until IsEmpty(ActiveCell.Value)
If ActiveCell.Value = "Green" Then
ActiveCell.EntireRow.Delete
End If
ActiveCell. Range("A1").offset(1, 0).Select
Loop
I would like a macro to find the columns named "apple" and "peach" and delete them. These would always be in row 1 but would always be in different column letters which is why I want the macro to simply find these columns by their name and not by their column letter.
And yes, I do mean the entire column altogether, shifting entire columns to the left. Wipe it off the face of the earth
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
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.
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 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 am using this to delete rows from my excel sheet. I basicaly start my script by placing xxx in cell B2000. I want to optimize my code and maybe some how tell excel do until last cell. I am playing with:
"Do Until lLastRow = Range("B" & Rows.Count).End(xlUp).Row" But not shure how to procede with this.
Range("B1").Select Do Until ActiveCell = "xxx" If ActiveCell = "Ticket" Then ActiveCell.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If Loop
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 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 large worksheet with data. Have data in the range L121:ED490.
I need to VB macro to check cells in column L,P,T,(every 4th column) until row 490 column ED and see if the cell is blank. If any cell in column L,P,T, and so on until column ED is blank then i need to make corresponding cell in column N,R, V and so on blank. Example : If L151 is blank then need to make N151 blank. If T152 is blank the need to make V152 blank.
I'm trying to create a short script which performs the following:
Searches for a specific name in column C on my "Resource List" sheet, then When this is found, check column H to see if this is populated,Then when both criteria are met delete the entire row.
Using a link I found here [URL], I've put together the code below:
[Code] ....
The problem I have is that although the criteria are being met the row is not being deleted.
Macro below would be to only go down to the cell = to the bottom-most cell with data in column A.
These columns below are all in row A (the title row) If column named "paper" says "white" OR "grey" AND column named "pizza" says "mushroom" AND column named "size" says "large" AND column named "flavor" says "butter pecan".. (again, only going down to the same matching cell with any data in column a)
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
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.
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 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
I have a long list that has all the services from our stores. I´m using the formula below to count how many services does each store have that are over 20 days old.
Now i´m wondering is it possible to make a macro that would delete all the cells in the range that do not match the criteria of my formula?
With one exception in sheet G cell A2--> has the number of the store. There are over 40 stores so each store has a number 10, 20, 30, etc... This is the part of the formula that determines witch store it is counting (G!$A$2:$A$6000=$B4) B4=10 in this case and B5 would be 20 and so on... By modifying that one part i can count old services for all of our stores.
Now I want the macro to delete all services from the list that are not over 20 days old and belong to the store 10 (B4). If it would delete everything that dont match that formulas criteria i could not get a list of old services for our other stores.
What i´m trying to accomplish is get a list of the services that are over 20 days old. I´m now picking them from the list by hand and that takes a really long time. Because of the number of the stores and the size of the range.
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.
I have a large spreadsheet that is 65000 rows and colomns A thru W. I need a macro to delete rows that if Date and Account and Type are equal and the net of Quantity is zero delete those rows.
Col A = Date Col C = Account Col D = Type Col J = Quantity
What the macro would do is if an account had 250 rows of activity on say 7/7/2004 (Date) and same type of account and the net of all Quantity is equal to zero delete those rows.
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.
Is 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 trying to delete columns or clear their contents after I meet a certain criteria in row 4. For example: I have one " Total" in row 4 that can be in any column. I wanna be able to delete whatever comes after "Total" in any row or column. Is there a macro that can do that?.......
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.
