Deleting Entire Rows Containing A Specific Value

Aug 29, 2007

I've been tasked with developing a macro that will generate schedules. For background, we're taking a QuickBooks Customer file and exporting it to be picked up by the macro. I've got that part working, but now I need to delete various rows based on certain criteria.

The spreadsheet has a field for Job Status which we use to define whether an account is under contract (2 in QuickBooks), or some other status. Basically what I need to do is delete the entire row of data where JobStatus (Column R) is anything but 2.

View 9 Replies


Deleting Entire Rows Once Filter Has Been Applied

Nov 24, 2011

I've got a report which is approx 40,000 rows long and I need to delete out alot of the info.

I need to delete out entire rows if column C contains a 0.

I've written a loop to do this but as theres so much data it take a long time to run.

Is there a smarter way to do this?

View 6 Replies View Related

Deleting Duplicate Cells (not Entire Rows)

Dec 17, 2007

I have a long list (over 1000 lines) of numbers, some of which are duplicated. Up to now I have been sorting them, adding in a simple check column to see if the number is a duplicate of the one below, and then manually deleting this cell.

Is there a better way to do this using VBA? The only way I have managed to find is by deleting the entire row, but I cannot do this as I have additional data to the right that I need to keep.

I also cannot install any add-ons as this on a work computer.

View 9 Replies View Related

Deleting Specific Rows..

Feb 21, 2009

Here's my problem. I have the following table:

View 5 Replies View Related

Deleting Specific Rows ....

Nov 9, 2008

I have created a worksheet (through an import into MS Excel 2007) which contains 287,281 rows. However, the data I need is located in rows: 4, 67, 130, ... (or n+63) rows.

View 10 Replies View Related

Deleting Specific Rows That Are Identical

Mar 8, 2012

I want to delete some rows that are identical. Aside from creating a macros to do this, is there a way to find a row with a specific criteria and delete it if value returns TRUE?

View 1 Replies View Related

Finding And Deleting Specific Rows

Jul 31, 2013

I was wondering if there is a formula or command to find and delet specific rows. I want to remove people from an excel email list.

On Sheet 1, I have 500 rows with columns for first name, last name, and email.
On Sheet 2, I have 30 rows with columns for first name, last name, and email that appear on Sheet 1 but need to be removed.

Is there a way to do this without manually searching for each email and then deleting the row?

View 4 Replies View Related

Deleting Specific Rows In Worksheet

Nov 28, 2006

i need to create a macro that will delete entire rows based on certain criteria. here is an example of some of the worksheet:

US0017651060AMR_CORP CM 12C
US0017658669AMR_CORP PR 8C


US0038813079ACACIA_RESH CM 12C
US0038812089ACACIA_RESH CM 16C



i want to create a macro that, when searching the third column of data (containing "CM" and "PR"), if a group of rows (between blank rows) do not contain at least one "PR", delete entire group of rows.

Therefore, the rows with "ACACIA_RESH" and "ACE_AVIATION" would be deleted from the worksheet.

View 9 Replies View Related

Conditional Formatting Entire Rows Based On Specific Columns?

Mar 25, 2014

I have been trying to find a way of formatting an entire row based on the contents of cells in each column. However i come unstuck when trying to make the column dynamic. Below is an example:



The idea is that i say current year is 2011 and all rows where C contains an a will turn green. What i would like to achieve is that when i change current year to say 2012 the conditional formatting adjusts so that it looks at D instead of C.

View 3 Replies View Related

Moving And Deleting Entire Rows Between Tabs In Workbook And Moving Them Back If Needed

Sep 23, 2013

I have an excel work book with 6 tabs. I would like to have Excel move an entire row from one tab to another tab (removing the row and inserting it in the other tab). I.e. Example I have a tab with items that are marked as "Open Actions" so if I were to change the drop down to close. Excel would move that entire row of actions to the tab with the "closed actions" and insert into the next available row. Now if someone were to come back at a later date say no it should be reopened than I would change the drop down to open and excel would move that row back to the open actions tab into next available row. I tried a PIVOT table and no good I played with few macro and not.

View 5 Replies View Related

Deleting Groups Of Rows When Specific Text Data Is Populating Row?

Feb 14, 2013

I have a complex report with thousands of lines I am trying to clean up. In order to do this, I need VBA that recognizes specific text data starting after row 9. When it sees this, it will need to select and delete the 2 rows above it, the row it is in, and the 6 rows below it. 9 rows in total, some of which are blank. The text will repeat every 47-50 rows, but I cannot say exactly when it will repeat again. When it is repeated, I need the 9 rows deleted. At this time, there is only 1 column in the report because I need to split out the data in the cell into adjoining cells. Since I have not solved that yet, I need to just delete the entire row.

The text is written with spaces between each letter and a double space between words. It reads, "B I L L I N G M A S T E R I N V O I C E S E T U P L I S T I N G"

View 8 Replies View Related

Deleting The Entire Row In VBA

Dec 24, 2007

I have a spreadsheet with thounsands of rows. Some of the rows I'd like to delete because thier values are below 5%. I wrote a VBA code, which primary function is to look for a cell and delete the row if the value is less than or equal to 5%. The code looks fine but I get an "Infinite Loop" anytime the first row value is not true. Below is the code,


Sub Delete_Zero_Factors()

Dim i As Integer
Dim CUSIP As String


i = 0

While Not (IsEmpty(ActiveCell.Offset(i, 0).Value))
CUSIP = ActiveCell.Offset(i, 0).Value

Do While (ActiveCell.Offset(i, 1).Value

View 9 Replies View Related

Deleting An Entire Column

May 2, 2006

Need the code for deleting multiple columns simultaneously in an excel depending on some search criteria.Suppose I want to delete the columns whose names are "Roll No" ,"Age" & "Class"

View 2 Replies View Related

Deleting Entire Row That Has Cells With Certain Strings?

Mar 16, 2013

I am trying to delete entire row that has certain text, let say "hi". I was able to delete cells with exact word "hi", but was not able to delete cells that have texts other than "hi". ex) "hi myname." Below is my vba code, and I keep getting run-time error and cannot execute the code.

Sub HiDelete()
Dim srchRng As Range
Set srchRng = ActiveSheet.Range("g:g")
For i = srchRng.Rows.Count To 1 Step -1
If worksheefunction.Search("hi", Cells(i, 7)) > 0 Then Rows(i).Delete
Next i
End Sub

View 3 Replies View Related

Deleting Entire Column - Statement Ignored

Apr 11, 2012

Working on some code that will find the Column Name "CL" and delete the entire column. Here is what I have so does not give me a error when it compiles, but for some reason the statement in red is being ignored. I tried switching Range with Columns, but that did not work.

Private sub coldelete ()
Dim Rng2 as Range

shUCLR = sheets("Unit Cost").usedrange.rows.count
Set Rng2 = Sheets("Unit Cost").Range("A1:BF1")

For Each d In Rng2
If d = "CL" Then
colnum = d.Column
colletter = Left(Sheets("Unit Cost").Cells(1, colnum).Address(False, False), (colnum)

View 2 Replies View Related

VBA Deleting Entire Row When Certain Word Appears?

Dec 12, 2012

I currently have the following code that works great and deletes the row from my spreadsheet when the word "vacant" appears in column F.

Sub test()
Dim r As Range
With ActiveSheet[code]....

The problem is, "Vacant" also sometimes appears in column G. So, as this code is now it is finding the word "Vacant" in either column F or column G and then deleting the row.

How can I write the code so that it only deletes the row where "Vacant" appears in column F but ignores it when it is in column G?

View 3 Replies View Related

Macro Is Deleting My Entire Sheet

Nov 15, 2006

Sub Delete()
Dim x As Long
For x = 28 To 8 Step -1
If Range("b" & x) = "" Then _
Range("b" & x).EntireRow.Delete
Next x
End Sub

View 9 Replies View Related

Deleting Entire Customer Entry If One Cell Is Blank?

Dec 24, 2008

I have a large database of customers. I would like to edit this database so that it only lists those customers that have an email address listed in that particular column. I can then save that as a separate list to upload to my marketing campaign.

Is there an easier way than just deleting entire rows at a time? About a 2,000 name list with maybe 30% having email addresses.

View 2 Replies View Related

Deleting Entire Columns Based On Cell Formatting

May 15, 2009

I'm trying to come up with a macro that can delete an entire column based on formatting of the cells. Basically what I have is a range of cells, and if all cells in a particular column have no fill color, then delete the entire column.

View 9 Replies View Related

Select Specific Columns Rather Than Entire Row

Sep 28, 2013

I plucked a macro off an old post from here (as I always do), but I'm having trouble tweaking it.

Sub completedtest()

Dim c As Range
Dim rngG As Range
For Each c In Intersect(ActiveSheet.UsedRange, Columns("i"))

[Code] ....

As it currently stands, the macro selects the entire row of rows containing the letter Y in column I. All I need to change is, I want to simply select columns B through I, rather than the entire row.

View 2 Replies View Related

Delete Entire Row Based On Specific Value?

Jul 18, 2012

Ihave a list of names in column A in certain rows instead of name there is an email address in this case I want to delete the entire row

View 4 Replies View Related

Deleting Last Cell In A Specific Row

Feb 24, 2009

hey everyone

i'm using a table like this one :


1 Main | value | value | value
2 Extra | value | value | value
3 Main | value | value | value
n Extra | value | value | value

i want to delete every cell in column "D" only when cell in column "A" contains "Extra" value.

View 6 Replies View Related

Find Duplicates In 1 Col Then Copy Entire Rows Of These Duplicate Rows?

Feb 2, 2014

The below code compares the Data in a Field that must be set and collect the duplicate Values in a second Worksheet.

The thing I want it to copy the rows, when a duplicate is found in Col A. editing the code below:

Original Sheet:
"A" "B" "C" "D"


View 7 Replies View Related

Copy Entire Row If Cells In Row Contains Specific String

Apr 3, 2013

I'm trying to copy entire row from sheet "source" to sheet "output".

Condition: If cell or cells in range (E7: lastcoll, lastrow) value is "A" then copy entire row.

Find the excel template in attachment.

My problem is that my macro is copying particular row, as many times as many "A" finds.

I want to copy entire row just once doesn't matter how many cells with "A" are in particular row.

'function to find last column a change letter of column to number
Private Function ColLetter(LastCol)
ColLetter = Split(Cells(1, LastCol).Address, "$")(1)
End Function

[Code] .....


View 3 Replies View Related

How To Find A Specific Text And Copy This Entire Row

Jul 12, 2007

I tried to writer my code myself but I have a long way to go. Here is what I must do for my case:

I want a macro to find the row that include a specific text (For example "SMSC") in a range (A1:A100) then copy this entire row below the cell which has the value "OTHERS". That is, if there are 10 pieces of "SMSC" so these rows including "SMSC" should be listed below the cell "OTHERS"

View 9 Replies View Related

Pull The Entire Row If It Finds The Specific Word

Sep 23, 2009

The following sub will look in the file ("FY09 SOF"), in column "A", search for the strings that begin with "2109", "3009", or ends in "-1", and copy the entire row. It will then paste these in the file ("FY09 PR Log Blank").

I also need it to look in column "C" in the ("FY09 SOF") file and pull the entire row if it finds the word "Payroll".

For some reason it will pull everything needed except the "Payroll" rows. What am I doing wrong?

Sub get_data()
Dim wb As Workbook, wbDest As Workbook
Dim ws As Worksheet, wsDest As Worksheet
Dim lngCalc As Long
Dim FoundCells As Range
Dim FoundCell As Range

Set wb = Workbooks("FY09 SOF")
Set wbDest = Workbooks("FY09 PR Log Blank")
Set wsDest = wbDest.Worksheets("Paste all here, then sort")

With Application
.ScreenUpdating = False..........................

View 9 Replies View Related

Highlight Entire Row When Any Cell In 1 Column Contains Specific Value

Aug 24, 2008

Code that will highlight a entire row when a cell from a column has a value of 0. It will check all the cell from Column C1:C100 and check all the cells that contains a 0 value. Need to highlight the entire row that contains this cell value and fill it in orange color.

View 9 Replies View Related

Highlighting Or Deleting Specific Cells

May 16, 2009

I have Column A with 380 cells filled in with numbers (A1-A380) i.e. numbers like..456758, 567848 etc etc

Column B has a subset of those numbers, maybe 80-90 or so (B1-B90).

What I want to do is either highlight the numbers in Column A that also appear in Column B in yellow, OR delete them from Column A altogether

View 8 Replies View Related

Deleting Lines Containing Specific Numbers

May 8, 2014

macro that would keep all lines containing a number greater than zero in column D and delete all of the other lines?

View 9 Replies View Related

Avoid Deleting A Specific Worksheet

May 2, 2006

I have a workbook where everything is controlled from a single worksheet that I named "Start".

In the "Start" worksheet I have instructions and one command button. When this button is clicked, it will start the VBA code. The code will basically show a couple of userforms where the user will input some information and after the user clicks on a button on the last userform, the code will generate new worksheet and present the results in that new worksheet.

The user could do this as many times as he wants, each time creating a new worksheet or overwriting in an existing worksheet (except the "Start" worksheet). The user is able to delete any worksheets he want.

I need to write a code so that if the user tries to delete the "Start" worksheet, it will not let him/her because then there is no worksheet where I have the button to click to start the VBA code.

I tried protecting the workbook, but then it does not allow to add new worksheets either.

View 9 Replies View Related

Copyrights 2005-15, All rights reserved