Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete an entire row if a selected row doesn't have a specified value in a cell
I am looking to delete an entire row if it doesn't have a cell with
certain criteria in it... for example in a cell the value would be in a column: placed on hold How could I create a macro that would delete every row that did not have this value in it? Thanks! -Marcus |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete an entire row if a selected row doesn't have a specified value in a cell
Hi Marcus
Try this one for the activesheet If the vale "ron" not exist it delete the row Sub Example1() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow Step -1 If Application.WorksheetFunction.CountIf(.Rows(Lrow), "ron") = 0 Then .Rows(Lrow).Delete ' Delete each row if the value "Ron" not exist in the row (It will look in the whole row) Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub See also http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm wrote in message ups.com... I am looking to delete an entire row if it doesn't have a cell with certain criteria in it... for example in a cell the value would be in a column: placed on hold How could I create a macro that would delete every row that did not have this value in it? Thanks! -Marcus |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Specify a row based on data from a cell to delete entire row..... | Excel Discussion (Misc queries) | |||
if a cell equal to zero how can it delete entire row | Excel Programming | |||
Delete entire row based on cell color | Excel Programming | |||
Highlight the entire row of a selected cell | Excel Programming | |||
Delete entire row based on cell content | Excel Programming |