Delete Rows Based On Rules

Aug 21, 2007

I am trying to clear row with the following rules:

the last cell must not contain 0, if 0 delete entire row.
the first cell the the A row should be only three character long, if more delete entire row.

Now I managed to delete the 0 in the last cell and but cannot get the VBA to count the characters in the first row and if more than three, delete the entire row . I know to count the characters in a cell is done by Len(Rng.Value) , but getting the VBA right is alluding me .

the script is here,


Sub Del_rows_with_zero_in_column_of_activecell()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Const StartRow As Long = 1 'Row to Start looking at
Dim StopRow As Long
Dim Col As Long

View 9 Replies


ADVERTISEMENT

Calculating A Discount Based On Certain Rules

Feb 11, 2009

I'm looking for assistance with working out the following formula. I live on an apartment complex in Marbella Spain and we pay Monthly Community Fees of say $100.

We want to calculate a penalty amount for late payers of say 30% of the monthly fee due for non payers? So for instance if the monthly amount due in Cell D8 = 100 and the actual amount received in cell E8 shows 0 or then increase any figure due for the following month in Cell F8 should increase by 30% ($ 130) in the following month. and so on

I use Excel ver 2003. Trust my query is reasonably clear?

View 7 Replies View Related

Excel - Date Validation Based On Rules

Nov 29, 2011

Validation on Excel

The User enters the date in cell D5 - I would like excel to validate the date based on the following rules:

1. Format "dd/mm/yyyy"
2. Prevents entering any weekend dates
3. When the user enters the date it can be the previous or current months date range

I have tried several ways in excel but I can't get anything to work. If an error occurs I want an alert message to appear.

View 7 Replies View Related

Automatic Formatting Of Cells Based On Rules / Criteria

Jul 22, 2014

I have a long set of data that I extract a "table" out of based on index matching controlled by 4 different criteria. This part is working as intended. However I need to format the cells with the corresponding "fetched data" based on another criteria. Makes sense? Have a look at a spreadsheet i made representing my problem. It has comments for the problems.

excel tableformatting problem.xlsxβ€Ž

View 1 Replies View Related

Randomly Select Cells Based A Couple Rules

Nov 6, 2007

I have a sheet that has 13 players listed in rows down column A. I have 6 columns each representing a quarter in a game. I was wondering what the best way would be to randomly select 5 players in each column and to distribute each cell selection as equally as possible over the 6 quarters.

So what I am doing is having excel designate equal amounts of playing time for each player each game.

Is this possible.

Can I base this off of previous games?

View 9 Replies View Related

Delete Rows Based On Criteria & X Rows Below

Mar 5, 2008

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

End If
Next Row_Counter
End Sub

View 9 Replies View Related

Delete Rows Based On Values In Rows Below

Jun 20, 2008

I am copying a price list from a worksheet. I currently have a script that deletes unwanted rows (products) but these products' header rows' are left. I also want to delete these text based headers. One solution might be a script that reads a columns cell value in the row(s) below and if values are missing the header row should be deleted.

View 2 Replies View Related

Excel 2010 :: 3+ Conditional Formatting Rules For Cell Range Based On Formula From Other Cells

Feb 5, 2013

how to apply conditional formatting via VBA to a range of cells based on input from another range of cells. Obviously this would be easy in Excel 2010, but I'm still using 2003 at the office and it needs to stay in this format to be readable by other users:

For cells M8:EK8, my conditional formatting
condition 2: Formula Is =AND($E$8>=M2,(($E$8-$D$8)>=(N2-$M$2))), color index is 40
condition 3: Formula Is =AND($F$8<=M2,$G$8>=M2), color index is 39
I want to add:
condition 4: Formula Is =AND($H$8<=M2,$I$8>=M2), color index is 40
condition 5: Formula Is =AND($J$8<=M2,$K$8>=M2), color index is 39
and so on

The cells in the range M8:EK8 are blank, they only get colored based on input added to D8 to K8. If there is no input, then the cells should be uncolored.

resource tracking ex.jpg

View 1 Replies View Related

Delete Rows Based On A Condition

Jul 20, 2009

Is it posibile to make a macro that automaticaly delete rows based ona a condition. Condition is to delete rows that have B column value "" or zero. For example:....

View 12 Replies View Related

Delete Rows Based On Two Criteria Within Each Row

Sep 24, 2009

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...).

View 8 Replies View Related

Delete Rows Based On Criteria?

Jan 23, 2010

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.

The department column might be in any column.

View 14 Replies View Related

Delete Rows Based On Content

Jan 5, 2009

Can a macro be used to:

1. Delete rows that contain certain text in a worksheet ?
2. Highlight a cell a colur based on a response ?

I have attached an example of what i mean .....

In the Audit Protocol worksheet is the main information - which contains questions and answers .... If a yes, n/a or no is selected then a response is automatically generated in the observations column (thanks to SHG for helping me with the formula for this) ..... How do I then get the cell to change to red if a NO response only is selected ?

Then the information from the Audit Protocol worksheet (the observation column and number column) are copied across to the worksheet named Action List (this is done just by the copy function) .... How do I go about deleting rows that contain "no action required" - as these are not needed for the report to be generated ?

View 11 Replies View Related

Delete Rows Based On Two Conditions

Mar 29, 2012

I need a macro that loops through all rows (With the exception of the first row) and looks for two conditions. If those two conditions are met, I'd like for it to delete the row.

The first condition is if Column O does not contain a certain phrase "VTX" VTX isn't the complete value of the column, it might just be contained within a few other notes in that column.

The second condition is if Col S equals "COMPLETE/FOLLOW-UP IMAGING".

So if these two conditions are met, Col O does not contain the word "VTX" and Col S equals "COMPLETE/FOLLOW-UP IMAGING", then that row should be deleted.

View 9 Replies View Related

Delete Rows Based On Cell Value?

Jul 6, 2014

I am looking to make a cell with the Value =Today()-1. I then want the code to pick up the cell value and then delete any row that does not have that value.

So for Example it would only keep =Today()-1 Dates in the sheet and delete the rest.

Code:

Dim LR As Long, i As LongWith Sheets("Sheet1")
LR = .Range("Q" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
If .Range("Q" & i).Value = "Y" Then .Rows(i).Delete
Next i
End With

View 9 Replies View Related

Delete Rows Based On Values

Nov 10, 2008

I have a 20,000 row text file that I import. It has blank rows, page headers and footers, and many rows at the bottom that I do not want. I have been cleaning up the data manually but this takes a great deal of time and I have a need to perform this clean up on a more frequent basis.

I have tried recording a macro to do some of the deleting but I am running into an issue that I do not know how to over come. When I filtered the data based on "Blanks" on a specific column and then try to delete them in mass, Excel errors out stating that my request was too complex.

View 11 Replies View Related

Delete Rows Based On 2 Criteria

Nov 30, 2006

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

View 3 Replies View Related

Delete Rows Based On Two Columns

Dec 22, 2006

I have a spreadsheet that in column A has either the letters "C", "LP", or is blank.

In Column L it has either numbers 1 through 100, or the letter "D".

What I need it to do is to check and see if the data in column L is "D" and in the same row column A is blank, then delete that row.

Also, if the data in Column L is "D" and the Data in column A is either "C", or "LP", then I need it to change the "D" to "1".

It might be easier to do it separately because once you run the part that deletes the rows that don’t have a “C” or “LP” then I could probably just do one that says if column L = “D” then change to “1”.

Lets see if I can simplify this for you.

If Column L = “D” and Column A = BLANK then Delete Row
(this would delete all “D’s” that don’t have the “C” or “LP”)

THEN

If Column L = “D” then change to “1”

View 9 Replies View Related

Delete Rows Based On Condition

Jul 3, 2007

complete this coding?

Sub DeleteBlankRowInColumnA()
'this macro scrolls down Column A if the cell is empty the row is deleted.
Dim A As Integer
A = 0
Do Until A = 142
If Range(0, "a1") = 0 Then
Selection.Delete shift:=xlUp
End If
Loop

End Sub

View 9 Replies View Related

Delete Rows Based On Criteria ..

Jul 19, 2007

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.

View 9 Replies View Related

Delete Rows Based On Data From Two Columns?

Sep 21, 2012

two things on the attached sheet.

First problem: in column B, I have a formula based on contents of column A. However, the limitations of 7 nested IFs leaves me with errors - I need 3 more IFs. Is there a way around that?

Second problem: I need to show the row of the first (earliest time in column A) and the last (latest hour in column A) record for each name. I've been sorting by name and time, then manually deleteing the rows I don't need. I know there's a better way, but I can't find it!

View 6 Replies View Related

Delete Duplicate Rows Based Only 2 Columns.

Feb 18, 2010

I need to filter the following file.

I need to remove all rows where COL A value and COL B value are the same. COL C does not need to be considered. However I need to retain one of the Col C values for purposes of formatting.

The end result should look similar to columns F,G and H!

View 2 Replies View Related

Delete Rows Based On Value In Multiple Sheets?

Apr 2, 2014

I have the following code to delete rows based on the value "No" in cloumn "L". This code should loop through all sheets and delete the corresponding rows.

[Code]....

The code works perfectly but with one little issue. It fails in the first run always with the following error message.

VBA error.png

I need to comment out the line

[Code] ....

continue it with a breakpoint to "End With", remove the comment. Then it loops all worksheets and deletes the rows.

There is no protection or similiar on any sheet.

View 1 Replies View Related

Delete Rows Based On Data In Two Columns

Mar 18, 2014

I have a spreadsheet with hundreds of rows. Columns C and D contain either TRUE or FALSE. I want to be able to automatically delete the rows where both columns are FALSE.

View 1 Replies View Related

Delete Rows Based On Fill Color

Aug 21, 2008

i found a link to my problem in the subject title below,

http://www.ozgrid.com/forum/showthre...t=45580&page=2

The guy at the end of the post says he got it working in the end, but no one seems to have posted the final solution - could someone tell me if the solution is actually in the thread, and if so where!

My problem is outlined by this guy - basically i want to be able to delete rows in a range that have no fill color, and so keep the ones that are colored.

View 9 Replies View Related

Delete Rows Based On User Input

Mar 6, 2009

1) Allow user input of a number between 1 and 999

2) Search column three and delete any rows that don't match this number.

I have some code that would delete any rows within a certain column, based on predefined criteria and i've tried modifying it to suit my needs.

View 7 Replies View Related

Code To Delete Rows Based On Condition

Apr 24, 2009

I have an Excel spreadsheet that is given to me weekly, but I'd like to remove repetitive and empty rows before presenting it to someone. It is set up like this:

Row 1, A1, contains the word Project.
Row 2 is blank
Row 3 is blank
Row 4 is blank
Row 5, A5, contains the word Organization.
Row 6 is blank
Row 7, A7, contains a 6-digit number starting with 3.
Row 8 is blank

(all of the above starts over again (loops) approximately 30 times)
The final row contains the phrase "Grand Total"

*Row 5 is repetitive and is not required. I'd like to delete it.

I have too many empty columns. Getting rid of them (and Row 5) would greatly shorten my spreadsheet.
I am aiming for:
Project
301111

Project
301112

Project
301110
(Repeat until finished)

Grand Total:

View 4 Replies View Related

Delete Rows Based On A Text Criteria

Aug 24, 2009

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?

View 8 Replies View Related

Delete Unwanted Rows Based On Value In Cells?

Sep 28, 2011

I am aware this is probably very simple, but I haven't been able to find a usable macro yet. I would like the data in the workbook that is not "Tuesday" (column L) and is not "3" or "4" (column I) to be deleted. All rows that have "Tuesday" and either "3" or "4" should not be deleted. Each row that is not deleted must have Tuesday in column L. All other cells are blank intentionally. The workbook will remain the same titled WKBK1 (no need to put the output data in a separate workbook nor separate spreadsheet).

Do you know what the macro would be so I don't have to copy and paste a formula over a large number of cells?

WKBK1
A B C D E F G H I J K L 2
3

[Code]....

View 8 Replies View Related

Macro To Delete Rows Based On Alignment?

Mar 4, 2012

I need a macro to identify the word "(blank)" based upon alignment (here it is left most) and to delete the next 4 rows only.

View 6 Replies View Related

Delete Rows Based On Criteria On Another Sheet?

Nov 21, 2012

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?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved