delete cells based on text value of adjacent cell

[Tony12zz]

I am trying to get the following code to delete cells if the cell in column A has a certain value and the cell next to it in column B is equal to 0.75, but it is not working properly

Any ideas?

Sub Deleter1()

LastRow = Range("A65536").End(xlUp).Row

For x = LastRow To 1 Step -1

If Range("A" & x).Value = "Test" And Range("B" & x).Value = 0.75 Then _
Range("A" & x).Select
Selection.ClearContents
Range("B" & x).Select
Selection.ClearContents


Next x

End Sub

 

[Seti]

You need an END IF after you clear the selections.

 

[Tony12zz]

With an END IF it brings up an error message saying end if with no block iF?

 

[erik.van.geit]

deleting cells or clearing the contents is not the same

at the end of the "IF"statement you putted a "_"
==> your macro only executes OR the first line under OR the next three lines

you probably ment to write
If Range("A" & x).Value = "Test" And Range("B" & x).Value = 0.75 Then 
Range("A" & x).Select 
Selection.ClearContents 
Range("B" & x).Select 
Selection.ClearContents
end if

cleaning up your code, you have

Sub Deleter1()

Lastrow = Range("A65536").End(xlUp).Row

For x = Lastrow To 1 Step -1
If Range("A" & x).Value = "Test" And Range("B" & x).Value = 0.75 Then
Range("A" & x, "B" & x).ClearContents
End If
Next x

End Sub

do you want to delete than write delete instead of clearcontents. Celss will be deleted and cells under will "climb up"

If there is a lot of rows to do post back for faster code
but it would be good to first understand what this code does
go therefor in the VBAeditor, click in your macro, anywhere and push the function button F8 to see line per line what happens

hope this will help you,
Erik

 

[Tony12zz]

Thank you Erik, that works fine now


Regards

 

[erik.van.geit]

You're welcome,
that's the way I can learn too,
bye,
Erik