View Full Version : Solved: clear data in data based upon a condition

07-21-2008, 02:26 PM
I have data in columns A, B, and C from 2 to 20,000. I need a macro that clears ONLY the columns A, B, and C of the specific row in question if the C value is negative.

I attached a sample of data if this seems too abstract or if I didn't explain this well.

Thank you in advance for any help.

Bob Phillips
07-21-2008, 02:46 PM
Public Sub DeleteDuplicateRowsUsingAutofilter()
Const TestColumn As Long = 3
Dim cRows As Long

cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row

With Cells(1, TestColumn)

.Resize(cRows).Formula = "=D1<0"
End With

Columns(TestColumn).AutoFilter Field:=1, Criteria1:="TRUE", Operator:=xlAnd

With Range(Cells(1, TestColumn + 1), Cells(cRows + 1, TestColumn))
End With


End Sub

07-21-2008, 03:05 PM
The code works great but I have cells D to ... Protected and I have to take off my protections for it to work. Or is there something else I could do :think:
I will look into it.

Bob Phillips
07-21-2008, 03:08 PM
Nope, take it off at the start, put it back at the end.

07-21-2008, 03:13 PM
Will do.

Thank you so much.:beerchug: