Hello Community:

I several columns of data. I need an excel macro that will look at a
specified column (I:I) and delete all cells that are a value I specify.
Each column of data will have a different criteria, so I'll just paste one
code after the next and change the column name and criteria.

I've looked through the discussion and can only find code to delete entire
row. I tried to modify the code to just delete cells and it did not work.

Can anyone help?

Thanks!

Right click the sheet tab, view code and past this in and run it

Simply change MyColumn & MyValue to the required column and value to delete
If MyValue is a string then put it in Quotes""

Sub marine()
Dim MyColumn As String
Dim MyRange As Range, MyRange1 As Range
MyColumn = "I"
myvalue = 222
lastrow = Cells(Rows.Count, MyColumn).End(xlUp).Row
Set MyRange = Range(MyColumn & "1:" & MyColumn & lastrow)
For Each c In MyRange
If (c.Value) = myvalue Then
If MyRange1 Is Nothing Then
Set MyRange1 = c
Else
Set MyRange1 = Union(MyRange1, c)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If
End Sub

Mike

"Sojo" wrote:

Hello Community:

I several columns of data. I need an excel macro that will look at a
specified column (I:I) and delete all cells that are a value I specify.
Each column of data will have a different criteria, so I'll just paste one
code after the next and change the column name and criteria.

I've looked through the discussion and can only find code to delete entire
row. I tried to modify the code to just delete cells and it did not work.

Can anyone help?

Thanks!

On reflection you may want this

MyRange1.clearcontents

instead of

MyRange1.Delete

Mike


"Mike H" wrote:

Right click the sheet tab, view code and past this in and run it

Simply change MyColumn & MyValue to the required column and value to delete
If MyValue is a string then put it in Quotes""

Sub marine()
Dim MyColumn As String
Dim MyRange As Range, MyRange1 As Range
MyColumn = "I"
myvalue = 222
lastrow = Cells(Rows.Count, MyColumn).End(xlUp).Row
Set MyRange = Range(MyColumn & "1:" & MyColumn & lastrow)
For Each c In MyRange
If (c.Value) = myvalue Then
If MyRange1 Is Nothing Then
Set MyRange1 = c
Else
Set MyRange1 = Union(MyRange1, c)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If
End Sub

Mike

"Sojo" wrote:

Hello Community:

I several columns of data. I need an excel macro that will look at a
specified column (I:I) and delete all cells that are a value I specify.
Each column of data will have a different criteria, so I'll just paste one
code after the next and change the column name and criteria.

I've looked through the discussion and can only find code to delete entire
row. I tried to modify the code to just delete cells and it did not work.

Can anyone help?

Thanks!

Hi Mike H:

I tried, but it didn't run. All I did was change Mycolumn to "A", Myvalue
to 6 (no quotes) and Range(MyColumn & "1:" to 6. In other words clear values
that in column A (starting from row 6 to that last row) that are greater than
6. I hit the play button and nothing happens. I even went back to sheet 1
and tired running it from the tools, macro menus. Nothing.

Also or other columns I am going to need to use less than (<) a value, and
between 2 values (<8 but 4).

I'm using Excel 2003.

Thanks!


"Mike H" wrote:

On reflection you may want this

MyRange1.clearcontents

instead of

MyRange1.Delete

Mike


"Mike H" wrote:

Right click the sheet tab, view code and past this in and run it

Simply change MyColumn & MyValue to the required column and value to delete
If MyValue is a string then put it in Quotes""

Sub marine()
Dim MyColumn As String
Dim MyRange As Range, MyRange1 As Range
MyColumn = "I"
myvalue = 222
lastrow = Cells(Rows.Count, MyColumn).End(xlUp).Row
Set MyRange = Range(MyColumn & "1:" & MyColumn & lastrow)
For Each c In MyRange
If (c.Value) = myvalue Then
If MyRange1 Is Nothing Then
Set MyRange1 = c
Else
Set MyRange1 = Union(MyRange1, c)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If
End Sub

Mike

"Sojo" wrote:

Hello Community:

I several columns of data. I need an excel macro that will look at a
specified column (I:I) and delete all cells that are a value I specify.
Each column of data will have a different criteria, so I'll just paste one
code after the next and change the column name and criteria.

I've looked through the discussion and can only find code to delete entire
row. I tried to modify the code to just delete cells and it did not work.

Can anyone help?

Thanks!