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.

 

[ColaQui]

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).

Thank you.

Yes, I wanted both lines to be run (only clear rows when A=1). However, with the code, only the first line run, the second line does not run, i.e. no matter A is equal to 1 or not, all rows for col G to I are cleared....

 

[Joe4]

If you have the code written exactly as shown, columns B-D and G-I will be cleared. If they are not, then something else is going on, like:
- some of the cells are protected and cannot be edited (confirm all these columns can be edited)
- the rows you are checking are not between row 6 and myLastRow (confirm the value of myLastRow)
- there is another macro running to re-populate the cells or deleting rows afterwards (so rows move up)

I would recommend stepping through the code line-by-line by using F8 where you can watch what each line is doing.

 

[ColaQui]

If you have the code written exactly as shown, columns B-D and G-I will be cleared. If they are not, then something else is going on, like:
- some of the cells are protected and cannot be edited (confirm all these columns can be edited)
- the rows you are checking are not between row 6 and myLastRow (confirm the value of myLastRow)
- there is another macro running to re-populate the cells or deleting rows afterwards (so rows move up)

I would recommend stepping through the code line-by-line by using F8 where you can watch what each line is doing.

Hi Joe4,

Thanks a lot for the valuable and helpful hints. I re-wrote the code to

'Then Range("L" & i & ":Q" & i & ",C" & i & ":F" & i).ClearContents

It works now! Thank you.

 

[Rick Rothstein]

Thanks a lot for the valuable and helpful hints. I re-wrote the code to

'Then Range("L" & i & ":Q" & i & ",C" & i & ":F" & i).ClearContents

You can write that more compactly and, in my opinion, in a more readable format this way...

Then Intersect(Rows(i), Range("L:Q,C:F")).ClearContents

 

[ColaQui]

You can write that more compactly and, in my opinion, in a more readable format this way...

Then Intersect(Rows(i), Range("L:Q,C:F")).ClearContents

That's indeed much nicer! Thank you

 

[Rick Rothstein]

You can write that more compactly and, in my opinion, in a more readable format this way...

Then Intersect(Rows(i), Range("L:Q,C:F")).ClearContents

That's indeed much nicer! Thank you

Actually, it just occurred to me that this can be written even more compactly using Evaluate's square bracket notation like this...

Then Intersect(Rows(i), [L:Q,C:F]).ClearContents