Deleting rows if a cell value = "x"

Posted by Steve Bridge on October 02, 2001 6:13 PM

Hi,

I am trying to write a macro (simple, really) that will take my worksheet (with the headers: PartNumber, Manufacturer, Quantity, Price, DateCode...) and find all values in the Manufacturer column, and if = to "MO" or "IT" then DELETE that row. OR, if that value = "AM" then multiply value in "Price" column by 1.05.

I need to do this with a macro. I will create a button in Personal.xls and run it from there.

Thank you much for your help.

Steve

Posted by Anon on October 03, 2001 7:00 AM

Try this :-

Dim rng As Range
Application.ScreenUpdating = False
Columns(1).Insert
Set rng = Range([B2], [B65536].End(xlUp)).Offset(0, -1)
With rng
.FormulaR1C1 = "=IF(OR(RC[1]=""MO"",RC[1]=""IT""),"""",IF(RC[1]=""AM"",1.05*RC[3],1))"
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, 2).EntireRow.Delete
On Error GoTo 0
.Copy
.Offset(0, 3).PasteSpecial Paste:=xlValues
.Delete
End With

Posted by Anon on October 03, 2001 7:33 AM

Correction ....

Correction :-

Dim rng As Range
Application.ScreenUpdating = False
Columns(1).Insert
Set rng = Range([B2], [B65536].End(xlUp)).Offset(0, -1)
With rng
.FormulaR1C1 = "=IF(OR(RC[1]=""MO"",RC[1]=""IT""),"""",IF(RC[1]=""AM"",1.05,1))"
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, 2).EntireRow.Delete
On Error GoTo 0
.Copy
.Offset(0, 3).PasteSpecial Paste:=xlValues, Operation:=xlMultiply
.Delete
End With