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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,890
Office Version
365, 2010
Platform
Windows, Mobile
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
 

Tody03

New Member
Joined
Jun 21, 2014
Messages
48
thanks it is working. however the first 0.2 is not included in the clear command
 

Tody03

New Member
Joined
Jun 21, 2014
Messages
48
thanks you did it
 

Tody03

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,890
Office Version
365, 2010
Platform
Windows, Mobile
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
 

Tody03

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

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,576
Messages
5,487,656
Members
407,608
Latest member
kjw

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top