Using Macros To Delete Cells And Rows Based On Specific Criteria?

May 7, 2012

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

[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

View 9 Replies


ADVERTISEMENT

Macro - How To Delete Rows With Specific Criteria

May 21, 2012

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

View 2 Replies View Related

Sheet Of Data - Delete Rows That Do Not Meet Specific Criteria

Nov 29, 2011

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

[Code] ..........

View 3 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 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 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 Where Cells Don't Contain Specific Value

Feb 27, 2013

If I have a column of data called "ColStream" and want to delete all rows that don't contain the string "Production", whats the best way to achieve it?

I have tried a for each loop, with INSTR but as this moves down a cell from the cell whose row has just been deleted, skipping the "NEXT" row in the range, it doesn't give the correct results.

Incidentally, the end game is to copy all the remaining cells to the cell immediately below a cell called "StreamsDatabaseStart" in a worksheet called "ProdStreams" should anyone out there be feeling particularly charitable!

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

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 But Save Info Based On Three Different Criteria

Aug 13, 2012

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?

View 6 Replies View Related

Macro To Delete Rows Based On Multiple Criteria?

Jul 10, 2014

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

View 6 Replies View Related

Macro To Delete Rows Based On Multiple Criteria

Apr 9, 2009

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.

View 9 Replies View Related

Delete Opposite Rows Based On Multiple Criteria

Feb 29, 2008

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

View 4 Replies View Related

Delete Rows With Blank Cells In A Specific Range

Feb 23, 2010

I need a VBA to delete rows with blank cells within columns F - AZ
Columns A - E contain headers but also need to be deleted if cells in columns F -AZ are blank.

View 9 Replies View Related

Delete Duplicate Rows Based On Multiple Cell Criteria

Apr 21, 2007

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

View 7 Replies View Related

Delete Rows Based On Criteria While Referencing Column Header

Jun 6, 2007

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

View 6 Replies View Related

VBA To Populate To Specific Cells Based On Criteria

Jul 6, 2014

I am looking for a VBA that could populate from column (A2:A5) to cells (B2:F5) as in the attached spreadsheet, based on the frequency, in a five year time-frame.

screen_print.JPG

[Code] .....

View 1 Replies View Related

Delete Certain Cells Within Row Or Column Based On Criteria?

Nov 7, 2013

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

View 4 Replies View Related

Run / Loop Through Cells And Delete Based On Criteria

Mar 4, 2014

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

View 4 Replies View Related

Loop Through Sheets And Convert Specific Formula Cells To Values Based On Criteria?

Jan 9, 2011

I looking for a macro that will go through multiple sheets & change specific cells to values if the column header is = to value set in specific cell.

for example

I would like the macro to look at row 3 in each tab (page 1, page 2, page 3) and if the value you is equal to X (parameter input on different sheet) then change the formula to a value in row 6 & row 12 of that column.

I'm attaching an simple example that i looking for this on. The green cells are the one i would like to change to a value.

Book1.xlsx

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

Simple Delete Rows Based On Cells Value

Apr 3, 2013

I am simply trying to delete all rows that have a value of less than 16 in the cell value's first 2 characters (column G).

Start with:
18N
18K
16K
16K
16K
14L
14L
13L
12L

End with:
18N
18K
16K
16K
16K

The code I am using:

Sub DeleteUnneededRows()
Dim c
For Each c In Worksheets("Sheet1").Range("G1:G500").Cells
If Left(c.Value, 2) < "16" Then
c.EntireRow.Delete
End If
Next
End Sub

but it is not working.

View 7 Replies View Related

Delete Rows Based On Adjacent Blank Cells

Aug 8, 2008

I would like a macro that deletes rows based on having two blank cells in adjacent columns. I have achieved this with the following code however i need it to only delete rows below a certain row. How would i achieve this?

Sub DeleteBlankARows()
Dim r As Long
For r = Cells(Rows.Count, 30).End(xlUp).Row To 1 Step -1
If Cells(r, 3) = "" And Cells(r, 4) = "" Then Rows(r).Delete
Next r
End Sub

View 2 Replies View Related

Delete Rows With Specific Characters In A Specific Column

Dec 10, 2007

Currently I am using the Kickbutt VBA Find Function of Aaron, but I would like to have something that works more efficiently. What I currently do is (assuming all possible values for Column J are A - F):

Find_Range("A", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("B", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("C", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("D", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("E", Columns("J"), MatchCase:=True).EntireRow.Delete

although I just want some code that says: delete all rows except those that have "F" as content in Column J. I already tried something like:

Range("1:65536").Select
For Each cl In Range("J:J")
If cl.Text = "A" Or cl.Text = "B" Or cl.Text = "C" Or cl.Text = "D" Or cl.Text = "E" Then
Rows(cl.Row).Delete
End If
Next

but it also takes much to long. The major problem I think, is that the number of records is variable so I search the entire worksheet...

View 5 Replies View Related

Excel 2010 :: Delete Duplicate Rows Based On Values Of Cells

Jun 17, 2014

I'm new to VBA and macros, using Excel 2010, and am trying to figure out how to delete all duplicate rows in a sheet where 2 or less of their values in column A is "1". I'd like have a script that is flexible enough to change to 3 or less if need be. I also have a header row that needs to be offset in the process.

A---B-
0--123 <-delete
0--123 <-delete
0--123 <-delete
1--123 <-delete based on this the value of column A
0--123 <-delete
0--123 <-delete
1--321
1--321
1--321
1--321
1--321

or

A---B-
0--123 <-delete
0--123 <-delete
1--123 <-delete
1--123 <-delete based on this the value of column A
0--123 <-delete
0--123 <-delete
1--321
1--321
1--321
1--321
1--321

View 5 Replies View Related

How To Create Macros Buttons To Add Or Delete Columns And Rows

May 21, 2014

So I'm trying my hand at creating VBA buttons and functions that do the following.

Button 1 :
to add a column with every click. The button will create one extra column. So by design there will be at least 1 column. Example if the button is clicked twice it will create 3 columns. I just need a buffer col. (contains no data)

Button 2 :
to delete empty columns leaving only one empty buffer column. That is this button will purge empty columns except the buffer column. OR if ALL the columns are filled, Button2 will create the buffer column.

Eg. I click Button 1 three times. It creates four columns. I only fill two columns. When Button 2 is clicked it will delete all the empty colums except the buffer column. OR I fill in all four columns, there is no buffer column, when I click Button2 it will insert the buffer col.

Button 3 :
to add a row with every click. The button will create one extra row. So by design there will be at least 1 row. Example if the button is clicked twice it will create 3 rows. I just need a buffer row. (contains no data)

Button 4 :
to delete empty row leaving only one empty buffer row. That is this button will purge empty row except the buffer row. OR if ALL the columns are filled, Button4 will create the buffer column.

Eg. I click Button 3 three times. It creates four rows. I only fill two rows. When Button 4 is clicked it will delete all the empty rows except the buffer rows. OR I fill in all four rows, there is no buffer rows, when I click Button4 it will insert the buffer row.

In the sample file there are six columns filled with data. The 7th col is empty(the buffer column). The column after that calculates the average of data in filled columns. There are eight rows with data and the ninth row is the empty buffer row.

The "add" buttons must be able copy the format/formula of the column/rows BEFORE the buffer colums/rows and insert it before the buffer column/row.

View 3 Replies View Related

How To Allow Users To Add / Delete Rows / Columns In Sheets With Macros

Mar 11, 2014

I have a sheet with macros that allows users to fill in data from columns F10 to O10 from row 10 to 54.

Column P will then make some computations based on the inputs from col F to O.
Row 55 will also make some computation based on inputs from row 10 to 54.

How to I modify the code below to allow users to either add/delete columns and rows such that it does not fowl up the computations?

The computation does not have to be at column P or at row 55, they can be dynamic and change depending on how many rows and columns the users delete/add.

A little background on what the code does. From Column F to O, the code UNLOCKS the columns only if user inputs date in Cell 3 of that particular column.

Example. Column F is locked from F10 to infinity at the start. Only when user inputs date into F3 does the cells F10:F54 is unlocked for user to input data.

This goes on until column O. I want users to be able to add more columns, or delete ones they don't need.

View 2 Replies View Related

Macros Restricted To Specific Cells

Apr 2, 2007

I've got a couple of macros that are allowed to be executed ONLY when specific ranges/cells are selected. Is there a way to make a msg box pop up saying: "Wrong Area Selected" etc. if the User selects a prohibited range or cell and there tries to run the macros?

View 2 Replies View Related







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