Deleting Rows Based On Cell Content

Oct 17, 2007

I wish to remove the page headers that were imported along with the raw data. Here's the code I've been using.


Column_To_Check = 3
Start_Row = 1
End_Row = ActiveSheet. Cells(Start_Row, Column_To_Check).CurrentRegion.Rows.Count
MsgBox End_Row
Search_String = "."
For Row_Counter = Start_Row To End_Row
If ActiveSheet.Cells(Row_Counter, Column_To_Check).Value <> Search_String Then
ActiveSheet.Rows(Row_Counter).Delete
Row_Counter = Row_Counter - 1
End If
Next Row_Counter
End Sub

When I import the data from the text file, there are 3 dimensions separated by a period in the same field. I.E: ABC.DEF.GHI and I use the break points to seperate as such: ABC|.|DEF|.|GHI|. Therefore, all rows that contain data also contain a period in cell C, but headers don't.

The Problem


End_Row = ActiveSheet.Cells(Start_Row, Column_To_Check).CurrentRegion.Rows.Count

CurrentRegion only selects up to the first blank row. However, I need this to select the whole sheet, not just the current region.

View 9 Replies


ADVERTISEMENT

Excel 2003 :: Deleting Or Displaying Rows Based On Cell Content One By One

Mar 20, 2014

I have a company with upto 5 products, and I have created a dropdown box where you select your product.

When I select product 1 i would need info on product 1 , when i select product 2 I need info on product 1 and 2 and continue. so for product 5 -i need info on product 1-5.

I tried to add all info on excel and tried to delete it one by one by deleting rows in excel for products but it does not work.

View 4 Replies View Related

Deleting Cell Content Based On Entry In One Cell?

Mar 22, 2014

I have a sheet that I fill out with customer data then print and start over with the next customer. This requires me to tab and delete through the sheet before starting the next entry and I am wondering if there is some way to auto clear the unlocked cells based on a single entry IE when we entered new data in the 1st field this would clear the unlocked cells and make them ready for new data?

View 14 Replies View Related

VBA To Hide Rows Based On Cell Content

Jun 23, 2006

If I have data in range A1:Z99 I want to write a macro to hide all the rows that have a zero in the A column.

View 9 Replies View Related

Deleting Rows/content Of Rows

Dec 11, 2009

Say I have a method that iterates through a bunch of Sheets. I check the name of every sheet, if it starts with "Data", I need to make everything between A4 and AZ500 empty (either by clearing cells or deleting rows doesn't matter how, as long as the result is an empty sheet below A4).

View 2 Replies View Related

Deleting Rows Based On Value Of Cell Within That Row

Nov 14, 2008

I would like to delete rows in a worksheet based on a value of a cell within that row. The worksheet will not always have the same number of rows. I will be looking at the value of each cell in column "AD", which is a date, and if it is not equal to 00/00/0000 then I want to delete that row.

View 7 Replies View Related

Deleting Rows Based On Cell

Apr 19, 2012

This is the final code needed...

Col G = Product #
Col H = Description

if Col H ( description ) has 'CVP' or "CO2" or 'CKN' = delete row

unless....

Col G is 617367, 858556, or 882949 then keep that row..

View 7 Replies View Related

Deleting Duplicate Rows Based On Cell Values

Nov 28, 2013

I have a sheet with 45,000 rows. Let's say each row has 4 columns: Create_timestamp, Update_timestamp, email_address, and o_flag

Many rows have duplicate email addresses. I would like to remove all the duplicate rows, EXCEPT for the row with the most recent Update_timestamp.

And actually, if I could just "hide" all those rows, that would be even better, but I'd be happy just figuring out how to delete all the "old" rows, so I just have a list of unique email addresses, with their create/update timestamps and o_flag column. Seems like this is such a basic use case for "Remove Duplicates,",.

View 1 Replies View Related

Excel 2010 :: Deleting Rows Based On Cell Value

May 21, 2012

I'm trying to delete a range of rows based on the value of a given cell. For example, if cell AC1 shows 5000, then I'd like to delete (not just clear) the range A5000:AA1048576. Or, if cell AC1 shows 10000, then I'd like to delete range A10000:AA1048576. I've come up with what is below but I don't think this is the right way to do it. I am using Excel 2010.

Code:
Range("AC1").Select
ActiveCell.FormulaR1C1 = "=COUNT(C[-27])+2"
Calculate
Range("AC1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A" & Range("AC1").Value:"AA1048576").Select
Selection.Delete

View 5 Replies View Related

Deleting Content Of A Cell Till The End Of Right (Not Row)

Nov 8, 2009

I need help on Excel 2007 Macro , the below coding searches for a string "recvtiming" in a cell as a wildcard and delete it entire row once it founds it in a single active sheet.

Question,

1. How do I manipulate it to search for the same string and upon finding

a) Delete its cell content and all the contents from its right hand of side till its last column which have a value from the same row on all 50 sheets in the workbook.

e.g
a b c d recv 0 1 2 3 4

It should return,
a b c d

View 6 Replies View Related

Change Content Of One Cell Based On Content Of Another

Apr 18, 2014

Formula/macro/etc that would enable me to have content of a cell changed based on the content of another cell in the same row.

Example: cell in column D says "PSA" - so I would need the cell in column H for that same row to read "Radio"

I would need an entire sheet scanned to review for these occurrences and make the appropriate changes. I also would need the formula to be inclusive enough to scan for variations in column D cell content (PSA 1, PSA 2, etc).

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

Move Rows Based On Content Of 2 Cells

Oct 24, 2006

At the moment I have 1 spreadsheet with 12 worksheets that I have to enter data into. I would rather enter all the data onto one sheet then have a macro move the data into the correct worksheet based on the contents of columns A and B.

The raw data will be entered into the worksheet "Data". Once complete, I would like the user to press a button and the macro to then identify from column A the suppliers name. If it is not one of the recognised suppliers, then it would move the row into the worksheet "one off". If it does recognise the supplier then it checks column B to see which of the two supplier's two worksheets it needs to copy it to, with the data entered into the correct column based on the column title (ie only the white columns).

View 9 Replies View Related

Deleting Rows Based On Their Value

Jun 26, 2008

I am having trouble with the .SpecialCells(xlCellTypeBlanks)

What I need to do is for a macro to first go through a range ("G8:G50"), Add a 0 value to any blank cell.

Then I want the macro to delete the entire row for any of the cells that have a value of 0 in that range of "G8:G50"

View 9 Replies View Related

Running Total Based On Content From Column For Over 1700 Rows?

Mar 26, 2014

Currently have a sheet with column A/B. Column A has any of the following letters to represent Wins, Losses, Ties, Overtime Loss, Shootout Loss - W, L, T, OTL, SOL. Column B has team point totals after each result. So after the win in row one, the team has 2 points. After the losses in rows 2 and 3, the team still has 2 points. After win in row 4 the team has 4 points. After win in row 5 the team has 6 points. After tie in row 6 team has 7 points.

W
2

L
2

L
2

W
4

W
6

T
7

What is the best formula or is there a formula to easily calculate running total based on content from column A this for over 1700 rows?

View 9 Replies View Related

Deleting Rows Based On Two Criteria?

Jan 30, 2013

I have a column with dates (dd.mm.yy) and I have a column with names. Moreover, several dates and names are repeated. What is needed, is to delete all the rows in which the difference between dates is smaller than 1825 days (5years) for the same name. (Namely, if I have three rows 01.01.1996 - "A"; 01.01.2002 - "A" ; 01.01.2005 - "A" I want all the rows with "A" to be deleted)

View 5 Replies View Related

Deleting Rows Based On Date

Feb 24, 2014

I run a daily report that contains material usage and I copy that into a workbook designed for Data Analysis. I only keep a years worth of usage in the workbook so I need part of my macro to look at todays date and delete anything that is older than a year.

View 4 Replies View Related

Deleting Rows Based On Value In Columns

Apr 28, 2009

I'm sure this is simple code, but...

I have a data table with over 20,000 rows that refreshes each day. I need to run a bit of code when it refreshes that says if the value in column R is 2, then delete that row.

View 14 Replies View Related

Deleting Rows Based On 2 Columns

Oct 9, 2011

I need to delete rows that are blank, 0, or contain errors (#REF, #N/A, etc). My problem is that once in a while, an error will show up in a singe cell of a row that I need to keep so a simple deletion of only errors/blanks/0 will not work for me.

The two cells I need to check for errors, blanks, and 0's are D and E. There can be an #N/A in col D and a 0 in col E or both D and E can have errors. Blanks will occur in all cells of the row.

I have tried to use the code below but it doesn't do anything, but it also doesn't give me an error.

Code:
Sub DeleteAll()
Windows("Template.xls").Activate
Dim r As Long
For r = 2 To 36500
If Range("D" & r).Value = "#N/A" And Range("E" & r).Value = "#N/A" Then
Range("D" & r).EntireRow.Delete = True

[code].....

View 1 Replies View Related

Deleting Rows Based On Set Of Conditions?

Dec 30, 2011

I have the following data

Ticket NumberOriginSectorDescFB DataPax NameClassPointO CCRegionDestin
976533060397MAADXB-DFWCDHJKL3SD RAJESHFirstDFW---
976533060397MAALIT-DFWCDHJKL3SD RAJESHFirstDFW---
976533060397MAADFW-DXBCDHJKL3SD RAJESHFirstDXBUS--
976533060397MAADFW-LITCDHJKL3SD RAJESHFirstLITUS--
976533060397MAADXB-NBOCDHJKL3SD RAJESHFirstNBO--NBO
976533060397MAANBO-DXBCDHJKL3SD RAJESHFirstDXB---
976533060398MAADXB-DFWCDHJKL3SD RAJESHFirstDFW---
976533060398MAALIT-DFWCDHJKL3SD RAJESHFirstDFW---
976533060398MAADFW-DXBCDHJKL3SD RAJESHFirstDXBUS--
976533060398MAADFW-LITCDHJKL3SD RAJESHFirstLITUS--
976533060398MAADXB-NBOCDHJKL3SD RAJESHFirstNBO--NBO
976533060398MAANBO-DXBCDHJKL3SD RAJESHFirstDXB---

I need to delete rows based on following conditions, If,

Ticket number, origin, FB Data, Desc, pax name is same i need to delete all rows except the Row which has Destin filled in. In the above example I need rows only with NBO

Result should be as follows,

Ticket NumberOriginSectorDescFB DataPax NameClassPointO CCRegionDestin
976533060397MAADXB-NBOCDHJKL3SD RAJESHFirstNBO--NBO
976533060398MAADXB-NBOCDHJKL3SD RAJESHFirstNBO--NBO

I need one more criteria to be tested the above result. IF,

origin, FB Data, Desc, pax name is same and ticket no is A1+1 i need to delete the second row

Final result should be

Ticket NumberOriginSectorDescFB DataPax NameClassPointO CCRegionDestin
976533060397MAADXB-NBOCDHJKL3SD RAJESHFirstNBO--NBO

View 2 Replies View Related

Deleting Rows Based On Criteria

May 29, 2012

I have this excel file where I every day have over 10.000 rows. I have 2 sheets, one called "Data" and once called "Include list".

The Data sheet contains a list of all of our customers and their customer IDs. The Include list sheet should contain the Customer ids which I want to keep in the Data sheet.

So what I have done is to loop through the Data sheet. If you are in the Include list sheet you should not be deleted fromt he Data sheet. If you are not then the row should be deleted.

I have actually done this and it works but the problem is it take a lot of time to run. I tested in earlier today and I had to break it after 20 minutes which is way too long for our users to wait.

This is the code I wrote:

Sub Include()
Dim FindString As String
Dim Rng As Range
Dim RowNr As String
Dim Lookup_x As String

Application.ScreenUpdating = False

[Code] ........

So my question is, is there any other way to speed this process up? The ScreenUpdating part I have tried but it didnt really speed it up as much.

View 1 Replies View Related

Deleting Rows Based On A Condition

Aug 8, 2012

I have a data dump. I download everyday with 200 rows and 20 columns. I need to remove any rows where there is no value in either columns C or D. Is there a quick way to do this?

View 7 Replies View Related

Deleting Rows Based On Two Criteria?

Apr 20, 2013

I have a very large spreadsheet (>10,000 rows) of data. I did Conditional Formatting based on duplicate values for the serial numbers column (B) and then sorted to "Put Selected Cell Color on top". Next I did a sort by the Last Scan Date column M (Oldest to New). The date/time format appears as follows

I used the following macro to delete rows with duplicate serial numbers but retain the row that has the newest time stamp. When I run my macro it's doing the opposite where it deletes rows with the newest time stamp and retains the oldest time stamp.

Code:

Sub Test()
'for Macro to Delete Duplicate Rows and Retain Unique Value
Dim LR As Long

[Code].....

View 9 Replies View Related

Deleting Rows Based On Conditional Formatting

Jun 11, 2009

I'm trying to get Excel to delete entire rows based on the conditional formating i'm using.

Basically, I want to get rid of all the rows that are of a certain color (let's say green, InteriorColor = 4). I've already come up with a way to delete rows based on color, but I have to take into account the Conditional formating i'm using.

Here's what I already have:

View 10 Replies View Related

Deleting Rows Based On If Match Found

Jan 23, 2012

I run a match formula which returns the row numbers of items i need to delete - at the moment, when a match is found, it will say "Match found, delete row 4" for example. Since multiple matches, potentially into the hundreds could be found - is there a way that i could run a script to delete the rows automatically when a match is found? So instead of putting the above "match found, please delete row 4" into an adjacent cell, could we just delete row 4? & then move onto the next row where a match is found?

View 9 Replies View Related

Deleting Rows Based On ListIndex Match

Feb 26, 2008

I need some assistance with the code below. I have a userform that has a listbox with 2 columns of information. When there is a match between the value in the listbox and the value in the cell for a given row, that row is deleted. I thought this was very straightforward, but i'm not getting this to work right...

Private Sub cmdDelete_Click()
Dim Val2 As String
Dim Val3 As String
Dim doit As Long
Dim roww As Long
Dim LastRow As Long
Val2 = ListBox1.List(ListBox1.ListIndex, 0)
Val3 = ListBox1.List(ListBox1.ListIndex, 1)
doit = MsgBox("Confirm that you want to delete the entry", vbOKCancel)
If doit = vbOK Then

View 9 Replies View Related

Deleting Rows Based On Expiry Date

Nov 19, 2008

I have a worksheet. Column A contains employee name, column B contains the date of the first day of the week's holidays they are booked for, i.e. 06/12/08 means they start their holiday for one week starting 06/12/08. That's probably really obvious, sorry.

Once a week I update the vacation list. I want to write a VBA code that looks at the date in Column B and deletes the entire row if it has occured. I'd like to use a value in C1 as the reference so if that cell has 06/15/08 in it then the code would delete every row where the date is prior to 06/15/08.

View 9 Replies View Related

Deleting The Rows Based On A Couple Of Variables

Nov 25, 2008

I have a worksheet ( adage inventory ) and I am trying to clean up some un-needed rows.

I need this deleting of rows based on a couple of variables.

I need to keep any row that Col F has the text "QCCONTROL"
and
I need to keep any row that COL N has the text "HOLD"
and
I need to keep any row that COL N has the text "REJECTED".

Sub Delete_OK_Lots()
lr = Sheets("adage inventory").Cells(Rows.Count, "A").End(xlUp).Row

For x = lr To 2 Step -1
If Sheets("adage inventory").Cells(x, "N") "HOLD" Or Sheets("adage inventory").Cells(x, "N") "REJECTED" Or Sheets("adage inventory").Cells(x, "F") "QCCONTROL" Then
Sheets("adage inventory").Rows(x).EntireRow.Delete
End If
Next x
End Sub

View 9 Replies View Related

Deleting Rows Based On Font Colour

Mar 30, 2009

I have 11 names, one name in each cell, in one row. I want to be able to delete a row if there are more than 3 names in that row that have the same font colour, eg red. Would have to be a loop type macro to check all of the rows on the one worksheet.

View 9 Replies View Related

Deleting Rows Based On Info In Certain Cells

Nov 29, 2009

I have a column that has different SIC codes in it (00045, 00046, etc). I would like to set up a macro or filter that would take cells in this column and delete each row that contains a certain code that I don't want.

Example, I want to delete all the rows with SIC codes that have 00345, 00873, 00145, etc in a particular cell. I would like a macro that would look at each cell in the column and delete the rows that I don't want.

View 9 Replies View Related







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