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

Tody03

New Member
Joined
Jun 21, 2014
Messages
48
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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:
Code:
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
 
Upvote 0
Maybe....

Code:
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
 
Upvote 0
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
 
Upvote 0
If you mean you want to clear column B as well as column C then try...
Code:
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top