Macro to clear contents based on cell value

[Schroetke]

I've searched the boards, and know the answer is right in front of me, but am stumped. Need VBA code for the following:

Column J contains a list of 1's or 0's based on other cells data. The range begins in row 6 and continues to the end of the sheet (which can be variable in length - but never more than 200 rows).

Beginning in row 6, I would like to clear the contents of O6 through Y6 if cell J6 =1. Then, clear the contents of O7 through Y7 if cell J7 =1, and so on to the end of the sheet.

Any help is much appreciated.

 

[Joe4]

This macro should do that:

Sub MyMacro()
 
    Dim myLastRow As Long
    Dim i As Long
    
    Application.ScreenUpdating = False
    
'   Find last row
    myLastRow = Cells(Rows.Count, "J").End(xlUp).Row
    
'   Loop through range
    For i = 6 To myLastRow
        If Cells(i, "J").Value = 1 Then Range(Cells(i, "O"), Cells(i, "Y")).ClearContents
    Next i
        
    Application.ScreenUpdating = True
    
End Sub

 

[Schroetke]

Thanks, Joe!
Works perfectly. I continue to learn, and truly appreciate the education.

 

[April99]

I also found and used Joe4's code. Thank-you very much Joe4.

Question: The code works perfectly for me except the "ClearContents" does not remove the fill colour from the cell. Normally, I thought "ClearContents" removed everything.

Would anybody know how to edit this code to remove the cell background colour as well as the cell contents?

Thanks!

 

[Joe4]

ClearContents only clears data/formula entries, not formatting.
To clear both the data entry and the fill color, try:

For i = 6 To myLastRow
    If Cells(i, "J").Value = 1 Then 
        Range(Cells(i, "O"), Cells(i, "Y")).ClearContents
        Range(Cells(i, "O"), Cells(i, "Y")).Interior.Pattern = xlNone
    End If
Next i

 

[April99]

Thanks again Joe4! That extra line of code worked.

But when you said ClearContents does not remove formatting that helped me spot what I was missing.

"Clear"

This also did the trick:

Range(Cells(i, "O"), Cells(i, "Y")).Clear

 

[ColaQui]

Thanks for the brilliant code, Joe4

It worked well for me. I want to clear content for multi ranges, I modified the code as below, but it won't work for row i, it will delete all contents from Col P to T, could you please help? Thank you.

For i = 6 To myLastRow
If Cells(i, "J").Value = 1 Then
Range(Cells(i, "O"), Cells(i, "Y")).ClearContents
Range(Cells(i, "P"), Cells(i, "T")).ClearContents

End If
Next i

 

[Joe4]

The way you have written your code, the line above it already clears the contents of columns O to Y of row i.
Since the next line addresses columns P through T of row i, that range is entirely contained by the one above it (O through Y), so the second line is completely unnecessary. The line above it should already be clearing that data range.
If it is not, your data range is probably protected (can you delete it manually?).

 

[ColaQui]

The way you have written your code, the line above it already clears the contents of columns O to Y of row i.
Since the next line addresses columns P through T of row i, that range is entirely contained by the one above it (O through Y), so the second line is completely unnecessary. The line above it should already be clearing that data range.
If it is not, your data range is probably protected (can you delete it manually?).

Hi Joe4, Thanks a lot for your reply. Sorry it was my mistake to cause confusion.

I want to clear content for two ranges: from "column B to D" and "column G to I", while keep the content in col E and F. When I add the blue text below, it works well for column B to D, but will delete all the stuff from column G to I without checking if Cell A i = 1 or not.... hope this make sense. Any advice? Thank you for your time and help.

For i = 6 To myLastRow
If Cells(i, "A").Value = 1 Then
Range(Cells(i, "B"), Cells(i, "D")).ClearContents
Range(Cells(i, "G"), Cells(i, "I")).ClearContents

End If
Next i

 

[Joe4]

Because both of your lines are within the IF THEN block, they are both subject to the check to see if column A is equal to 1.
So either, both lines will run (if the condition is met), or neither one will be run (if the condition is not met).