Delete Cell Data With Criteria?

Aug 2, 2014

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.

View 3 Replies


ADVERTISEMENT

Delete Last 10 Rows With Data (no Criteria)

Feb 14, 2009

I simply want to delete the last 10 rows containing date from a worksheet. No criteria other than they contain data anywhere in the row.

View 4 Replies View Related

Delete Every Cell That = Criteria

Jun 5, 2007

Delete every cell in the entire worksheet that = "Mail"

View 9 Replies View Related

Delete Duplicate Rows Using Data In Column A As Criteria

Jan 13, 2009

I have a spreadsheet that has thousands of rows of data with maybe 6 columns.

I would like to remove the duplicate rows but the problem I am facing is that I would like to use column A as the criteria.

There are some duplicates in column A but the issue is that the data in column B, C, D, etc may not be the same for each duplicate.

Easier to see in this image:
http://img127.imageshack.us/img127/6364/datajv6.jpg

So A1 is the same as A6
A1 has data in B1 and C1, no data in D1
A6 has data in B1, C1 and D1

I need to delete either row A1 or row A6, it is irrelevant which row it is but one of them has to go.

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 Formula If Cell Value Meets Criteria?

Feb 18, 2014

I'm using the following on the Worksheet_Activate event to update rows with the date, (Cell AD1 contains the current date):

Code:
Range("X2:X2000").Formula = "=IF(IF(ISBLANK(W2),,$AD$1)=0,"""",IF(ISBLANK(W2),,$AD$1))"

Is there a way I can amend it so it only updates those cells which currently have no data?

At the moment it is changing every cell with the date which overwrites existing dates - I want it to update the new stuff only.

View 3 Replies View Related

Delete Nth Row In All Worksheets If Cell Meets Criteria

May 21, 2008

I want to scan all sheets in a workbook and to delete a first row where a value in cell(1,1) is "table".

Sub DeleteFirstRowInWorksheet()
Dim SheetName As Worksheet
Dim i As Integer

For Each SheetName In Sheets
If Range("A1") = "table" Then
Rows("1:1").Select
Selection.Delete Shift:=xlUp
End If
Next SheetName
End Sub

It delete only in an active sheet. What's wrong?

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

Formula To Accept / Delete Cell If Numbers In Other Cells Meet Defined Criteria

Apr 21, 2014

I am looking for a formula that either accepts the number in a cell if the numbers on two other cells are "approved". In other words, I have on cell A1 my value to be tested. On cells A2 and A3 I have two numbers. On cell A4 I'm looking for a formula that copies the number on A1 if A2 is bigger then 0.25 and A3 is smaller then 0.35. In not, there's no copy or a N/A appears.

View 3 Replies View Related

Delete Multiple Columns By Column Name No Criteria Need, Just Delete Them

Jun 24, 2009

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

View 4 Replies View Related

Delete Row If Cell Contains Specific Data

Jul 27, 2007

I need a quick macro that will search column D for the words "balance forward" then delete the entire row. The macro needs to loop through many rows and it contain more than one "balance forward" reference.

View 9 Replies View Related

VBA Code To Delete Cells If No Data In Cell To Right

Aug 11, 2013

One more thing and I think I completed my project. I have a tab called comments.

Column A and B will always have data in them from 1-320.
Column C may or may not have data in it from 1-320.

Is there a code I could tie a button with that will delete the cells A and B if C has no data but leave it alone if C has data?

Ex: [URL] ........

When I click sort, only A1, B1, C1 and A5, B5 and C5 will still be there and everything else deleted.

View 5 Replies View Related

VBA To Delete Cell Content With Spisific Data

Jun 19, 2009

I need a VBA that i will attach to a button that will delete spisific data in the cell.

Eg... delete all the cell CONTENT in column A1:A65536 with the word "NONE" in them, it must only delete the word not the cell.

View 9 Replies View Related

Find Data, Delete It And The Adjacet Cell

Oct 13, 2006

I have the code below. and it works to delete the variable in DelWC which is selected by a combobox. Combobox is populated by Column R info, You select the data, and then hit the delete key, it deletes what it found, great.. but I need it to delete addition info that is in the next row adjacent to it...

Column R Column S
AINA2 X-5900
AENE2 X-5874

The code finds and delets the AENE2 but doesn't delete the X-5874

With Worksheets("Data"). Range("R:S")
On Error Resume Next
Set Del = .Find(DelWC)
If Del = Del Then
Del.Delete Shift:=xlShiftUp

View 2 Replies View Related

Use Cell Data For Filter Criteria

Feb 15, 2010

I have had no luck searching for an answer so better to ask. I am using auto filter on Sheet1 and filtering a column of dates. What I need is for the criteria for the auto filter to be pulled from cell H1 on Sheet 2. Is this possible or is auto filter the wrong direction to go? I am looking to find all the rows on Sheet 1 that contain the date found in cell H1 on Sheet 2. Then I am going to cut and paste that data to another worksheet. I think I have all the rest of it figured out but am stumped at the filtering portion.

View 9 Replies View Related

How To Get Data From A Cell Given A Specific Criteria

Jun 19, 2007

I have a big data sheet. I have approximately 2170 rows and 50 columns. In some rows I have a series of consecutive zeros. I need to build a formula that gives me the date ( located in row3) right before the first zero in the series of consecutive 12 zeros.

For instance in row 1340 I have a series of 12 consecutive zeros. The first zero happens in column AC, therefore I need the formula to retrieve cell AD3. however every rows has zeros located in different cells therefore I need a general formula.

View 9 Replies View Related

Delete Cell Data Based Off Expired Date

Aug 19, 2014

I need cell A1 data deleted once the date in B1 has passed.

View 7 Replies View Related

Copying Data From One Cell To Another Based On Criteria

Jun 22, 2014

I am trying to use VBA to move data from one column to another while using values of a third column as a reference for where each value in the first column should be moved to in the second. I will try to explain as best as possible, currently I have a macro that copies from one column to the required but due to the irregular spacing of the data it results in the data not matching with the data its being copied into.

Say I have Three columns A, B and C. I have a range of data in column A say dates that have irregular amounts of spacing between them. Column B is empty. Column C contains Data that is again spaced out irregularly and not in line with that of A. The value of column C cells is just identical values. Now the aim is to move the values from column A in the order which they are listed and place them in Column B next to each value in Column C. So say the repeated value in column c is EXAMPLE then everytime EXAMPLE appears in column C i want to move one date from column A into column B next to this EXAMPLE value, in order which it appears in column A. So the first date will move next to the first EXAMPLE value and the second date which appears next to the second EXAMPLE and so on and so on until it reaches the end of the sheet.

My thinking was placing the A column data in an array and doing some sort of sort.

View 3 Replies View Related

Using Cell Data Has Variables/strings For Criteria

May 23, 2007

I have a bunch of data in column A like this:

BBX3838
BBX3838
BBX3838
BBX3838
BBX4434
BBX3249
BBX3249
BBX3249
BBX3249
BBX2233

so then using this : ...

View 9 Replies View Related

Copy And Paste Row Of Data Where Cell Contains Criteria

May 8, 2008

I need to be able to search column y and rows 5-100 and where cells contain "A" copy and paste entire row to row 110. I am guessing it is really simple but I am obviuosly missing something

View 3 Replies View Related

Delete Whole Row, If Criteria Is Met

Jul 9, 2006

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

View 6 Replies View Related

Find Matching Criteria In Column And Use Data From Other Cell In Row

Dec 13, 2013

I have two work sheets I'm using. Sheet1!A column contains a list of customer names like "Smith, John".

Sheet2!A contains the same list of name with additional information found in the row. For example, you will find "Smith, John" in Sheet2!A3 and John's phone number in Sheet2!B3

In Sheet1!B it to find the associated telephone number based on matching criteria from Sheet1!A and Sheet2!A

For example, Sheet2 has the following data.

[Code] ....

On Sheet1 I have the following names. You'll notice some of the names repeat.

[Code] ......

In column Sheet1!B I would like it to scan column A from sheet2 and display the data show in column B. So it would look like this-

[Code] ....

You'll notice some names repeat but it always displays the correct number from Sheet2.

View 5 Replies View Related

Delete Rows Once Two Criteria Met?

Jul 31, 2014

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

Resource NameResource IDProject NameBusiness areapriorityAllocation hours
Lavanya 123243 MarketingSales 145
Lavanya 123243 Sales Sales 1150
Mukund 5678 Re engineeringRetail 210
Mukund 5678 StreamliningGeneric 990
Mukund 5678 documnentationDocumentation 850

View 5 Replies View Related

Delete Row Based On Criteria

Mar 14, 2014

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

View 7 Replies View Related

Delete Row Based On Criteria

Dec 10, 2012

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.

View 3 Replies View Related

Delete Rows Using Criteria

Oct 3, 2013

I have rows like below in Column A. I wish to cycle till row 700 and delete rows where Right(A2(,1)="X") and also Rows where there is a blank Cell.

5

22X3

65

260X

70X5

8X66

873X

2206

059X

088X

90X8

2283

7606

View 6 Replies View Related

Delete Rows After Certain Criteria

Jul 20, 2009

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.

GeoRgn refSub Rgn refCountry Country 2GroupENNR3NxaaSO claim-inENNR3NxbbSO claim-inENNR3Nx222SO claim-inENNR3Nx222SO claim-inENNR3Nx222SO claim-inENNR3Nx222SO claim-inENNR3NxaaSO normalENNR3NxbbSO normalENNR3NxccSO normalENNR3NxddSO normalENNR3NxeeSO normalENNR3NxffSO normalENNR3Nx111SO normalENNR3Nx111SO normalENNR3Nx111SO normalENNR3Nx222SO normal

View 9 Replies View Related

Loop To Delete Row When Criteria Is Met

Jul 13, 2006

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

View 2 Replies View Related

Delete Rows That Fit Criteria

Dec 9, 2007

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.

View 8 Replies View Related

Copy Data From Another Sheet If Adjacent Cell Meet Criteria

Aug 13, 2008

I have a have excel problem Im trying to figure out. I have 2 sheets. Sheet1 and Sheet2. I want to copy into sheet1 cell B2 the contents of sheet2 column C row x iff sheet2 column B row x = 1. There will be only one cell in that column equal to 1, so I will be copying that cells(colB; rowx) adjacent cell (colC;rowx) contents. This is what I have so far but it doesnt work. =IF('Sheet2'!B:B=1,DONTKNOW WHAT TO PUT,""))

View 5 Replies View Related







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