Code To Delete Rows Based On Status Code In Column

Jul 24, 2009

I'm trying to write a VBA script which will delete all rows in my Excel spreadsheet where Column I (which contains a status code) does not contain the word "Completed".

At the moment, I'm doing this the other way round: my script is able to search for entries in Column I which contain the status codes "Pending", "Awaiting Authorisation", "In Progress" etc and delete them. The idea is that when all those rows are deleted, I'll only be left with rows which have a status of "Completed". This works fine at the moment. However, the concern is that if a brand new status code is added to the data file, my script would be unable to pick it up and delete it. This is a small sample of the code I'm currently using (which deletes all the rows with statuses other than Completed):

View 4 Replies


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:


(Repeat until finished)

Grand Total:

View 4 Replies View Related

Code To Delete Rows Based On User Input

Aug 21, 2008

I am using the following piece of code to delete unwanted rows from a worksheet:

Sub DeleteDates()

FinalRow = Cells(65536, 3).End(xlUp).Row
For i = FinalRow To 1 Step -1
If Cells(i, 3).Value Like "*2007*"
Cells(i, 1).EntireRow.Delete
End If

Next i

End Sub

What I am working with is a sheet of about 5000 customer appointments going back to the beginning of 2006. A have peiced together a lot of code to format it exactly as I want and to create a pivot table of what is remaining after the rubbish has been deleted.

However I am finding that I need to keep amending and re-running this bit of code, depending on the date range I want to look at. For example the code above will delete all appointments made in 2007.

My question is: is there a way that I could incorporate an input box, whereby excel asks the user for a start date and an end date and deletes any rows outwith that range?

Some important info: All of the dates are in column C, in the format mmm yyyy

I would only need to narrow down to a month - so for example mar 2008 - jun 2008

View 9 Replies View Related

Modify And Enhance Recorded Code - Delete Rows Based On Value

Aug 22, 2012

I searched many sites and used codes which delete Rows based on criteria. In my case those codes works, BUT it took so much time about 30 min since there is about 75 thsd rows, and that solution in not time saver. I recorded code below and it is done in seconds. make it dynamic, VBA, since number of rows is every month larger. Basicly, I need code which delete rows based on Column B, where walue is "R"

Sub DelRowsBasedOnOneCriteria()
' DelRowsBasedOnOneCriteria Macro


View 4 Replies View Related

Excel VBA Code To Select A Range (rows) Based On Values And Delete

Aug 8, 2013

I am trying out with a code which checks for cell value as "Select" in column IU and then checks for corresponding column IV for value as "0". Please note that "Select" and "0" are populated by formulas. I need the select "Select" and "0" till the next "Select" occurs in column IU and delete the selected range and continue the process until last non empty cell based on column C.

I have written the below code but it doesn't work.

Public Sub Test()
Dim nRow As Long
Dim nStart As Long


I could have uploaded the excel file that I am working on but did not find any upload attachment option.

View 1 Replies View Related

Excel 2007 :: VBA Code To Delete Rows Based On Text Starting Content

Oct 25, 2013

Using excel 2007. I am interested in writing a VBA code to delete rows based on the text starting content. I would like to delete rows with cells that do NOT start with an "S" or "SA"



I would only like to keep the last 3 lines.

View 3 Replies View Related

Counting Rows Based On Date And Status Column

Apr 2, 2007

I wish to count the number of entries in a workbook based on the entries in two columns. The columns are for example H1:H100 ( date) and L1:L100 (status). I want to the number of times the date in H1:H100 is over 30 days old, as well as L1:L100 = 'Started' for example.

View 2 Replies View Related

VBA Code To Copy And Paste To Specific Rows Based On Matching Column Header?

Dec 7, 2011

I have a spreadsheet and I was wondering if there is a way to copy from a specific range in say A1:A5 in Sheet1 and paste into Sheet2 where the column matches cell A1 in Sheet1.

I am trying to do this in VBA and I was wondering if there is a way to do this.

View 9 Replies View Related

Code For Status Update

Apr 29, 2009

On a weekly basis I record payment terms updates and do this process manually. I compare 2008 terms against 2009 terms for any status update. I would like to provide a button to do the work for me. Can anyone provide code please?

I currently use this "IF Statement" =IF(C2=D2,"No Change",IF(C2D2,"Reduced")))

View 9 Replies View Related

VBA Code To Delete Certain Rows

Jan 22, 2009

I have an excel file that i need to delete some empty rows in the file. it looks like this: I have to delete THE EMPTY ROW above each custom table, and leave everything else as it is.

empty row
custom table
empty row
custom table
empty row
custom table

View 11 Replies View Related

VBA Code To Delete Rows

Apr 30, 2014

I am looking for a vba code that will delete rows for me. have data in column E and I need the code to look for any cell that has the word "DELETE" in column E. When it finds the word "DELETE", I need it to delete the entire row. For instance if the first instance of this in cell E41120, then it should delete that entire row and look for the next instance to do the same.

I have seen codes out there to delete rows, but I can't get any of it to work for my situation.

View 7 Replies View Related

How To Delete Rows Using VBA Code

Jan 1, 2009

I have data in cells Starting from

B1= Quality, C1= Size, D1= Quantity, E1= Length, F1= Width, G1= Thickness

My data comprises from B2:D2000 ( I am exporting data from SQL Server) so the data is in the horrible conditions , having blanks cells in these three columns . Columns E , F and G are empty.

Can someone help me in cleaning these empty rows in the three columns on the following conditions

delete entire row if column B, C and D contains empty or blank cells or zero
delete entire row if any cells in Column C and D contains text values
delete the entire row if Cells in colums A contains the word Map

the data in the column C is in the following format( Length x Width x Thickness)

Is it possible to get this data in the following form


View 9 Replies View Related

Delete Rows Code

Mar 26, 2009

(excel 2007)

I am using the following piece of code, which I have copied and modified from another workbook in which it worked properly.

Option Explicit
Sub DeleteRows()

'Delete Unneeded Rows
Dim lUsedRangeRows As Long
Dim lRowCounter As Long

With ThisWorkbook.Sheets("Data")
lUsedRangeRows = .UsedRange.Rows.Count

For lRowCounter = lUsedRangeRows To 3 Step -1 'work from the bottom up
If DateValue(.Cells(lRowCounter, 5)) < DateValue(.Range("PayDate")) Then
.Cells(lRowCounter, 5).EntireRow.Delete
End If
Next lRowCounter
End With
End Sub

I receive a "Type Mismatch Error" on the If DateValue line (highlighted red). PayDate is a named range for cell A2 on the same page.

View 9 Replies View Related

VBA Code - Loop Through And Delete Rows

Oct 12, 2013

I use this code to delete some rows, how I would modify it to work in another spreadsheet where it would "Loop" through and delete rows that start with "User:Kellcyna" down to where the rolls start with "Numbers", and delete the rolls that start with "Total cost center" down to where the rolls start with "Numbers".

The data can contain up to 50000 rolls at times.

Sub Finally()
Application.ScreenUpdating = True
[a:a].AutoFilter Field:=1, Criteria1:="="
If [a1] = "" Then [1:1].Delete
ActiveSheet.AutoFilterMode = False
[Code] ........

Here is a sample of the data I need the macro to work on. The rows I need deleted are the rows that are highlighted.

User: Kellcyna STANDARD HOURS BY COST CENTER Date: 09/29/2013 Time: 15:10:04 Page: 10


Order Op Emp Post Work ctr Setup Unit Planned Earned Total Actual Actual Actual Total Total Orde C R

# Date SU Unit Plnd Stds Setup Run Tme Brd Tme Prod Run Time E

104527059 0010 00000000 09/25/2013 HSW01 0.000 0.4 HR 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 PP01

104523849 0010 00000000 09/25/2013 HSW01 0.000 0.2 HR 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 PP01

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

View 5 Replies View Related

Code To Delete All Empty Rows

May 20, 2014

I need a code that I can run that will delete all rows that have nothing in at the bottom of the sheet. What I mean is I will be printing a file and because rows have been deleted that had data in a lot of sheets are being printed and wasted. So what I need is a before close code or something that will actually make the last row with data in the last row!

View 2 Replies View Related

Code To Delete Empty Rows

May 7, 2007

I have a database and at times I have empty rows. I am trying to write code that looks at the database (it may change daily) and deletes the rows that are empty. The code I've written does NOT work:

Sub emptyrows()
Dim emptyrows As Object
Set emptyrows = Cells(65536, 255)
If Rows = "" Then
Selection.Delete Shift:=xlUp
End If
End Sub

View 9 Replies View Related

Code Won't Delete All Blank Rows

Sep 25, 2009

When I tried this code on excel at home it worked, but now i'm at work and it dosen't delete any rows at all!

Here's the ....

View 9 Replies View Related

Fastest Way To Delete Rows In Code

Aug 17, 2007

I have a bit of code that deletes row by row and takes a long time to process. I've seen people suggest deleting by range processes more quickly. How can I modify this to delete by range?

lngLastRow = Sheets(2).Range("A65536").End(xlUp).row
For lngRowCount = lngLastRow To 1 Step -1
If Application.WorksheetFunction. CountIf(Sheets(2).Range("A1:A" & lngRowCount), Sheets(2).Range("A" & lngRowCount)) > 1 Then
Sheets(2).Range("A" & lngRowCount).EntireRow.Delete
End If

View 7 Replies View Related

Delete Duplicate Rows Code ...

Jun 4, 2008

I have trouble in deleting the duplicate rows. I have a code to find the duplicate values

Public Sub Unique_Proj() 'This is the first step which takes out the unique projects from the base data
'Call Work_Assignment
'this selects the unique projects in the sheet
Application. ScreenUpdating = False
Dim A, E, B(), n As Long
With ActiveSheet
A = . Range("g2", .Range("g" & Rows.Count).End(xlUp)).Value
Redim B(1 To UBound(A, 1), 1 To 1)
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For Each E In A
If Not IsEmpty(E) And Not .exists(E) Then
n = n + 1: B(n, 1) = E: .Add E, Nothing
End If
End With
Sheets("Unique Projects").Select
Range("G3:G" & Rows.Count).ClearContents
Range("G3").Resize(n).Value = B

End With
Application.ScreenUpdating = True
End Sub

However, this gives me a list of values in that perticular column only.

What I want is either delete the complete row which is duplicate OR select the entire range of values which are unique (based on the column searched) and paste it in a new sheet. The second option is more better for me.

View 7 Replies View Related

VBA Code To Delete Rows If Specific Value Is Not Present?

Aug 6, 2014

I'm trying to write a macro to update a report including deleting all rows that do not contain a specific value of TP in column K. Below is one of the examples.

[Code] .....

View 3 Replies View Related

Which Are The Variables In The Code, Delete All The Rows Which Have Data In?

Oct 28, 2008

This vba code will delete all the rows which have data in. At present it starts on A2 (so doesnt delete anything above A2. Which part of the code in red would I need to change for it to be A3 and which would I need to change for it to be B3.

View 5 Replies View Related

Code To Delete Rows After Selected Cell

Sep 24, 2011

I've got a sheet that I build from a weekly sheet. I can have 30 to 60 rows, but I always have 10 to 20 rows left over with data in column C, none in A, my last code is "Range("A1").End(xlDown).Offset(1, 0).Select" which takes me to the last cell in column A. Any code that I can then delete say 20 rows after that? I tried one code, but it wanted to delete everything below the cell selected which ran & ran.

"-" are blanks. Range moves the selected cell to A4. I want to delete row selected + 19 more.


View 9 Replies View Related

VBA Code To Delete Duplicate Rows In Workbook

Sep 8, 2013

I have a worksheet populated with data. I have sorted the data and have dupulicate values in column B,C,D up to H.
I have put in a formula to give me a 1 value in column A where there are duplicates. Is there a way to write a VBA code that will Delete all the rows that have a 1 in column A.

View 4 Replies View Related

VB Code To Compare/delete Rows Of Data

Feb 4, 2007

VB code to carry out the following task?

Look at name in cell BC2, check the name in cell BA2 and if is the same delete the name in BA2 and BC2.

If is different then leave both cells as they are and move onto checking BC3 against BA3
carry this loop thru BC2 : BC40

View 9 Replies View Related

VBA- Code That Will Delete Blank Rows Between Data

Apr 1, 2009

a code that will delete blank rows between data, below is sample of my data, the header row is 8

From this ....

View 9 Replies View Related

Macro Code To Amend The Status Of Cell To Be Date?

Mar 31, 2014

I have a user form we are using to transfer data into an excel spread sheet. When transferring the date from a text field it is formatted as general, I need this to be a date format DDMMYYY. The cell needing formatting is N2

View 5 Replies View Related

Code To Delete Rows - Reset Running Total

Dec 6, 2012

I have a sheet with several thousand rows. I want code that will do this:

- After finding a "D:" in column A (contained in the cell but will be at a different place each row), start a running total of values in column B.
- When finding "F0" in column A...
--if the running total of values for rows that have "D:" equals the value in column B with the "F0", delete the "D:" rows
--if the running total doesn't match, reset the running total and search for the next "D:"
- Repeat for all rows.

View 1 Replies View Related

Total Data- Having A VB Code To Delete Empty Rows

Mar 7, 2007

The spreadsheet I am working in has $ which I need to total. The information, or rows, can vary from day to day so there is no static place to have a cell with an @sum. Management wants me to add a pretty line that says

'Total' in one cell and the $ next to it. I have about 30 sheets with columns to total. Yuck, takes forever.

How do all of you total if you don't know the last row? At the top?

I thought it might work to put an @Sum in the last row of the spreadsheet, in the column

I need to total, and having a VB code to delete empty rows. That would be automatic.

View 9 Replies View Related

Speed Up Macro Code To Delete Duplicate Rows

May 2, 2008

I have a VBA Macro that loops through about 100,000 rows in an Excel file and removes rows that have a duplicate cell value. The macro takes about an hour and a half to run. Are there any ways to make the Macro run faster? Any ways in general to make VBA macros run faster?

View 9 Replies View Related

Code Runs When I Hide Rows Or Delete Cells

Aug 2, 2008

So i don't have any events that i know of that should trigger my macros to run. But everytime i delete a cell, or hide rows, it seems all my code/macros just auto run. And i had a few times when i hid the rows, the code would start, and then my excel application would crash.

View 4 Replies View Related

Copyrights 2005-15, All rights reserved