Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows with criteria
I want to delete all rows that do not have the word "gift" in column A. The
cells in column A often have "gift" along with some other words. It must be simple to write a wildcard that includes "gift", but I don't know how yet. Thanks for your help. Scott |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows with criteria
Try this
Sub Example2() Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False StartRow = 1 EndRow = 100 For Lrow = EndRow To StartRow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value Like "*gift*" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub You can also Filter (faster) Look here http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl "S.E." wrote in message ... I want to delete all rows that do not have the word "gift" in column A. The cells in column A often have "gift" along with some other words. It must be simple to write a wildcard that includes "gift", but I don't know how yet. Thanks for your help. Scott |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows with criteria
That's just about what I need. The only problem is that I want to delete the
row if it does NOT contain "gift". This macro deletes the ones that DO contain "gift". Scott "Ron de Bruin" wrote in message ... Try this Sub Example2() Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False StartRow = 1 EndRow = 100 For Lrow = EndRow To StartRow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value Like "*gift*" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub You can also Filter (faster) Look here http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl "S.E." wrote in message ... I want to delete all rows that do not have the word "gift" in column A. The cells in column A often have "gift" along with some other words. It must be simple to write a wildcard that includes "gift", but I don't know how yet. Thanks for your help. Scott |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows with criteria
Oops
ElseIf Not .Cells(Lrow, "A").Value Like "*gift*" Then .Rows(Lrow).Delete -- Regards Ron de Bruin http://www.rondebruin.nl "S.E." wrote in message ... That's just about what I need. The only problem is that I want to delete the row if it does NOT contain "gift". This macro deletes the ones that DO contain "gift". Scott "Ron de Bruin" wrote in message ... Try this Sub Example2() Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False StartRow = 1 EndRow = 100 For Lrow = EndRow To StartRow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value Like "*gift*" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub You can also Filter (faster) Look here http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl "S.E." wrote in message ... I want to delete all rows that do not have the word "gift" in column A. The cells in column A often have "gift" along with some other words. It must be simple to write a wildcard that includes "gift", but I don't know how yet. Thanks for your help. Scott |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows with criteria
Another thing. This seems to just clear the rows. In the macro I would like
to move the other rows up to take the place of the deleted rows. Scott "Ron de Bruin" wrote in message ... Try this Sub Example2() Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False StartRow = 1 EndRow = 100 For Lrow = EndRow To StartRow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value Like "*gift*" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub You can also Filter (faster) Look here http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl "S.E." wrote in message ... I want to delete all rows that do not have the word "gift" in column A. The cells in column A often have "gift" along with some other words. It must be simple to write a wildcard that includes "gift", but I don't know how yet. Thanks for your help. Scott |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows with criteria
Nevermind. Now it works. Thanks Ron.
Scott "S.E." wrote in message ... Another thing. This seems to just clear the rows. In the macro I would like to move the other rows up to take the place of the deleted rows. Scott "Ron de Bruin" wrote in message ... Try this Sub Example2() Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False StartRow = 1 EndRow = 100 For Lrow = EndRow To StartRow Step -1 If IsError(.Cells(Lrow, "A").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value Like "*gift*" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub You can also Filter (faster) Look here http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl "S.E." wrote in message ... I want to delete all rows that do not have the word "gift" in column A. The cells in column A often have "gift" along with some other words. It must be simple to write a wildcard that includes "gift", but I don't know how yet. Thanks for your help. Scott |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete all rows if criteria not matched | Excel Discussion (Misc queries) | |||
Delete rows if specific criteria not met. | Excel Worksheet Functions | |||
Delete rows based on criteria | Excel Discussion (Misc queries) | |||
Delete rows based on certain criteria | Excel Discussion (Misc queries) | |||
Delete rows w/o criteria | Excel Programming |