Clear Cell Contents based on Cell Value

[SDave]

Afternoon all,

I'm hoping someone can help me.

I need to be able to clear the contents of cells in column C, D, E, H if the value of column Q equals HELLO for arguments sake.

Clearing the contents of an entire row is not a problem, however when it comes to resizing rows I seem to come unstuck.

Related thread: http://www.mrexcel.com/forum/showthread.php?t=476993

Any help would be appreciated.

 

[meldoc]

Hello

I may have missed the point, and grossly oversimplified things.
So please try the below on a COPY of your data.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> SDave()<br><SPAN style="color:#00007F">Set</SPAN> sh = Sheets("Successors_for_Roles")<br>  lr = sh.Rows(sh.UsedRange.Rows.Count).Row<br>  Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> lr<br>      <SPAN style="color:#00007F">If</SPAN> Cells(i, "Q") = "HELLO" <SPAN style="color:#00007F">Then</SPAN><br>        sh.Cells(i, "C").Resize(, 3).ClearContents<br>        sh.Cells(i, "H").ClearContents<br>      <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br>  Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[SDave]

Thanks Brian, your a star. Much appreciated. That's exactly what I was after.

 

[Zenta]

Hi,

Can you please let me know how i can make it dynamic. I mean it should check each row one and by one with the range of 1-20000 rows automatically.

its very urgent...Many thanks in advance

Thanks
Zenta

Hello

I may have missed the point, and grossly oversimplified things.
So please try the below on a COPY of your data.

Sub SDave()
Set sh = Sheets("Successors_for_Roles")
**lr = sh.Rows(sh.UsedRange.Rows.Count).Row
**Application.ScreenUpdating = False
****For i = 1 To lr
******If Cells(i, "Q") = "HELLO" Then
********sh.Cells(i, "C").Resize(, 3).ClearContents
********sh.Cells(i, "H").ClearContents
******End If
****Next i
**Application.ScreenUpdating = True
End Sub