search for a specific cell and clear all the range till the end.

[Tody03]

in my worksheet column c contains 0.1 and 0.2. the column is sorted.
I need a macro that will find the first cell with 0.2, define the column from that point to the end and clear the data
Thanks

 

[My Aswer Is This]

What does this mean:
"define the column"

You mean the cells has only:
"0.2"

and clear all other cells below this cell is that what you want
And we will be doing this on the active sheet

Try this:

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
For i = 1 To Lastrow
If Cells(i, "C").Value = "0.2" Then Range(Cells(i + 1, "C"), Cells(Lastrow, "C")).ClearContents: Exit Sub
Next
Application.ScreenUpdating = True
End Sub

 

[MARK858]

Maybe....

Sub Find_02C()
    Dim FR As Range, LR As Range
    Set FR = Range("C:C").Find(What:="0.2", LookIn:=xlValues, _
                               LookAt:=xlWhole, SearchDirection:=xlNext)
    Set LR = Cells(Rows.Count, "C").End(xlUp)
    Range(FR, LR).ClearContents
End Sub

 

[Tody03]

thanks it is working. however the first 0.2 is not included in the clear command

 

[Tody03]

thanks you did it

 

[Tody03]

thanks again, it is working.
I need another correction:
after i defined all the 0.2 and i marked it all the way down, i want to include a parallel area on column b before i clear the whole content. hOW CAN i DO IT?
THANKS

 

[MARK858]

If you mean you want to clear column B as well as column C then try...

Sub Find_02C()
    Dim FR As Range, LR As Range
    Set FR = Range("C:C").Find(What:="0.2", LookIn:=xlValues, _
                               LookAt:=xlWhole, SearchDirection:=xlNext)
    Set LR = Cells(Rows.Count, "C").End(xlUp)
    Range(FR, LR).Offset(, -1).Resize(, 2).ClearContents
End Sub

 

[Tody03]

Thanks again Mark. it worked again and i solved a lot of time. My daily procedure was 18 minutes and with your help it went down to 10 minutes. Now i have to work and find how to make the macro work faster

 

[MARK858]

make the macro work faster

What macro?

 

[My Aswer Is This]

Your script must be doing a lot more then you showed us if it takes 10 or 18 minutes.
This little line of code should take less the 10 milliseconds.