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:
Project
301111
Project
301112
Project
301110
(Repeat until finished)
Grand Total:
View 4 Replies
ADVERTISEMENT
Jul 20, 2009
Is it posibile to make a macro that automaticaly delete rows based ona a condition. Condition is to delete rows that have B column value "" or zero. For example:....
View 12 Replies
View Related
Jul 3, 2007
complete this coding?
Sub DeleteBlankRowInColumnA()
'this macro scrolls down Column A if the cell is empty the row is deleted.
Dim A As Integer
A = 0
Do Until A = 142
If Range(0, "a1") = 0 Then
Selection.Delete shift:=xlUp
End If
Loop
End Sub
View 9 Replies
View Related
Aug 28, 2007
I have a worksheet with part numbers, and I want to delete all the rows with part numbers matching a part number in another workbook (I could import the list into the part number workbook in a new sheet if it makes it easier). The part numbers in the other worksheet would be in column A.
View 5 Replies
View Related
Oct 19, 2007
I would like to Use my Excel VBA program to search each row in a csv document for a name (located in a cell ) if the name exists then I would like to delete the entire row.
Whenever I try to do the above in excel, even when I save in csv format the file formating or something gets changed and the file which has to be procesed through another program then process incorectly.
Is there a way to do what I need while retaining the formating which must obviously be changed when the file is opened in excel?
View 9 Replies
View Related
Mar 1, 2013
Having book1 and book2 (that is closed). Is there a way that if I erase a value "X" in book 1, update book2 (without open it) deleting the complete row/rows where the value X is found?
View 4 Replies
View Related
Apr 26, 2008
I have a spreadsheet that calculates percentages and then outputs the results to a pie chart. There are 9 different percentages being graphed in cells A41 to A49. The chart looks weird if any of the percentages end up being 0, so I have the formula set to add 0.00001 to each calculation (so they show up as 0% and display on the chart as 0%, but truly are 0.00001). I would like it so that if any of these 9 percentages ends up being 0 (or really 0.00001) that the row automatically hides and thus won't display on the pie chart. How can I create a macro that automatically runs to accomplish this, and automatically updates as percentages are recalculated.
View 3 Replies
View Related
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
View Related
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
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"
Code:
Sub DelRowsBasedOnOneCriteria()
'
' DelRowsBasedOnOneCriteria Macro
[Code]....
View 4 Replies
View Related
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.
Code:
Public Sub Test()
Dim nRow As Long
Dim nStart As Long
[Code]....
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
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"
EX:
05S0128
06S0112
05S2298
S25852
S36963
SA36185
I would only like to keep the last 3 lines.
View 3 Replies
View Related
Jun 21, 2008
I have an imported report in a spreadsheet. It imports to three columns. I need to check each row in column A for three seperate criteria and delete the rows I don't need. I need to delete blank rows and check next row for page header info. Delete these and next rows to next blank cell. Check next row for page header and not delete if not page header. Several rows down will be a cell with 23 blank spaces before the word Reg: and sometimes other words past this but always this first. This row is to be kept. I looked at the FAQ's example of Deleting but I don't think it will work. I also need to put a key word in column A at a point where I want to stop. This report is a couple thousand rows long so a VBA procedure would really save time. I have a procedure I use to check for two zero's in two cells that hide these rows but I couldn't modify it to work on this report.
View 9 Replies
View Related
Oct 6, 2008
I'm working this spreadsheet and i want to automate of deleting the entire rows once the last two columns contains both Zero values. the worksheet cotains thousands of row to clean-up.
View 3 Replies
View Related
Dec 15, 2009
I want a macro to delete the entire row based on the value in Column Q ....
View 14 Replies
View Related
May 28, 2009
What I need is a macro to delete a row or rows for a specific range of cells (eg. E20 to E58) when the cells in that range column E is = blank. The cells in column E is formated with a currency sign $. Deleting the row should shift up all rows below. I need to run this macro manually. Excel 2002 SP3.
View 9 Replies
View Related
Jan 6, 2009
I want to create a macro the will delete all the rows in a sheet if 1 of 2 conditions are met. The first condition is if a cell is blank or if a cell contains the word "negative"
View 9 Replies
View Related
Nov 1, 2006
how to delete duplicate rows except the first and the last rows using macro..
I have data like this and i want to delete those rows except the first and the last rows
31/10/2006
31/10/2006
31/10/2006
31/10/2006
I use the below macros from btadams posted 27th January 2003 but only for delete the last row
Sub DleteDups()
Dim Cell As Range
Do While ActiveCell.Offset(1, 0) <> ""
If ActiveCell.Value <> ActiveCell.Offset(1, 0).Value Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.EntireRow.Delete
End If
Loop
End Sub
View 9 Replies
View Related
Dec 14, 2009
I want to delete a row in excel based on a condition.
The condition is as follows:
I have few rows in the excel where the columns 7,12,13,23 are same and column 24 in one row has some number and in some other row the column 24 is blank with orange color filled(the colorindex for orange is 44).
In this case I want the row with orange color filled in column 24 to be deleted.
View 11 Replies
View Related
Jan 19, 2007
i have an issue i need to delete entire row if a condition is met, i need syntax if possible to delete if a cell word "begins with" a certain letter ...in this case...letter A
column is "O"
must search threw entire row
View 3 Replies
View Related
Oct 31, 2012
Here is the problem: i have two conditions to satisfy first is data from column A and second is data from column C, what i wanted to do is if both data from column A and B has the same other data in column A and C then sum the total in column D, F, I retain data for column B, E, G and H. Then delete the duplicate rows. By the way we also need to replace the remove the text (W1,W2,W3,W4 and W5) in column C.
View 6 Replies
View Related
Jun 25, 2007
How do I have the marco delete rows if the O column = a certain cell.
For instance my O column has a bunch of colors
Cell N1 list one color (red) and I want the macro to delete the rows that contain the color from N1 in column "O".
View 9 Replies
View Related
Nov 21, 2006
I have a spreadsheet with 3023 rows and columns A-L. Here's a small sampling of Column A (with heading "sku" included, copied exactly as it appears in the spreadsheet):
A
sku
3102-0400-100000
3102-0400-200000
3102-0500-100000
3102-0500-200000
3102-0600-100000
3102-0600-200000
3102-0700-100000
3102-0700-200000
I need to delete every row in which column A includes the text "200000".
Although the above sampling shows "200000" appears in every other row, that is not the case in all 3023 rows.
View 9 Replies
View Related
Jun 16, 2014
I have table in which against the Column A data values are added in Column B, C and D.
If Column B,C and D cell values is 0, then I want entire row including A to be deleted. How can I do this in VBA?
Note: Column A data begins from Row 15 and below.
Just to avoid confusion. I want row deletion, only when all the three cell on B,C and D is = 0
View 7 Replies
View Related
May 13, 2009
I have a problem deleting rows based on a condition, i didn't know how to use offset method.
i'm using a table like this one :
A B C
1 Main design1 FFR0
2 Extra design2 FFR0
3 Main design3 FFR0
4 Extra design4 FFR0
.
.
n Main designn FFR0
I want to delete entire row if "FFR0" in column C exists in a row beginning by "EXTRA" (column A)
View 6 Replies
View Related
Apr 16, 2006
I want to be able to delete certain rows based on multiple criteria. If data in the row does not meat the crieteira, it shud be deleted. I have attached the excel file sample in which I want to keep the highlighed rows and detele the rest. Its base on Origin city and Destination city combination. Eg: If Origin city="A" and Destination City = "B", I want to keep the row. Like wise for all highlighted rows. but if the Origin City = "B" and Destination City = "U" I want to delete the row.
View 9 Replies
View Related
Jan 9, 2007
i have a set of data as below and wish to delete ENTIRE ROW if cell is the same but keep the first entry when code comes to it..ie in the data set below excel would keep first row and delete 2nd,3rd,5th, 6,7,8 and then go to next unique identifier which would be AU0000LIFHB3 this would not be deleted as it is unique the would proceed to AU300GPTC011. this process would then stop when no data was available
AU300AUS1019
AU300AUS1019
AU300AUS1019
AU0000LIFHB3
AU300AUS1019
AU300AUS1019
AU300AUS1019
AU300AUS1019
AU300AUS1050
AU300GPTC011
AU300GPTC011
AU300GPTC011
AU300GPTC011
AU300INTE018
AU0000LIFHB3
0
AU300SUNQ027
View 4 Replies
View Related
Apr 29, 2008
My worksheet has a report name in cell A1, a brand name in B2, price name in C2, and color in D2. Row 3 has a table header in cell range A3:E3 followed by rows of table contents at the end of which is a row of empty cells. This sequence repeats itself for a new product: a row with the report name in column A, followed by a row with a new brand name, price and color in the same columns as before followed by the same table header and table contents. The number of products (with the previously mentioned details and table) vary so I would like to write a macro that is not dependent on a set number of products. Each table varies in the number of rows but never contains empty cells and always ends with a row of empty cells. I want to delete all rows with the report name and have all products in one table with the brand, price and color added to the table header and contents.
View 2 Replies
View Related
Mar 28, 2008
I am sure this is a very simple questions. I am using the below code to work on the worksheets on a workbook called "MF BANK EXPOSURE SUMMARY.xls" so far the code that I am using is:
Sub Commandbutton2()
Dim iCol As Long
Dim Isheet As Long
Dim Item As Worksheet
Set MFBANK = Workbooks("MF BANK EXPOSURE SUMMARY.xls")
For Each Item In MFBANK.Worksheets
With Item. Range("A1:T65536")
For iCol = .Column + .Columns.Count - 1 To 1 Step -1
If IsEmpty(.Cells(65536, iCol)) And IsEmpty(.Cells(1, iCol)) Then
If .Cells(65536, iCol).End(xlUp).Row = 1 Then .Columns(iCol).Delete
End If
Next iCol
End With
Next Item
End Sub
I would like to extend the code so that it works on the worksheets of two workbooks at the same time the one being the "MF BANK EXPOSURE SUMMARY.xls" and the other being "MF CP EXPOSURE SUMMARY.xls" .
View 3 Replies
View Related
Jan 25, 2010
I would like a code which looks on the worksheet “Racks” and if Column A is "Y" then copy the entire row and paste it on the to sheet “Summary Racks”. I would need it to then find the next instance and then continue to copy and paste. I have attached the file I am currently working on.
View 4 Replies
View Related
