adjustment to this vba code

[Haydo]

Sub Find_scr_StrikethroughIt()
Dim SCR As Range
With Columns("G")
.Replace "scr", "=scr", xlWhole
Set SCR = .SpecialCells(xlCellTypeFormulas)
.Replace "=", "", xlPart
End With
SCR.EntireRow.Font.Strikethrough = True
End Sub


This code places a strikethrough in the text of all corresponding rows when a 'scr' is in column G

How do I adjust it to clear the contents of the entire row (instead of strikethrough)

Thanks in advance.

 

[Trevor G]

Try this out

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> clear1()<br><SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">With</SPAN> Sheets("Sheet1")<br>    LR = .Range("G" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> LR<br>        <SPAN style="color:#00007F">If</SPAN> .Range("G" & i).Value = "scr" <SPAN style="color:#00007F">Then</SPAN> .Rows(i).ClearContents<br>        <br>    <SPAN style="color:#00007F">Next</SPAN> i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[RSXchin]

nevermind

 

[Rick Rothstein]

Sub Find_scr_StrikethroughIt()
Dim SCR As Range
With Columns("G")
.Replace "scr", "=scr", xlWhole
Set SCR = .SpecialCells(xlCellTypeFormulas)
.Replace "=", "", xlPart
End With
SCR.EntireRow.Font.Strikethrough = True
End Sub

This code places a strikethrough in the text of all corresponding rows when a 'scr' is in column G

How do I adjust it to clear the contents of the entire row (instead of strikethrough).

That code looks like something I would have written. Just change the code line highlighted in red to this...

SCR.EntireRow.ClearContents

 

[Peter_SSs]

Assuming data starts in column A and there is a heading row, you could also consider this type of approach.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Clear_scr()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.UsedRange<br>        .AutoFilter Field:=7, Criteria1:="=scr"<br>        .Offset(1).ClearContents<br>        .AutoFilter<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Haydo]

That code looks like something I would have written. Just change the code line highlighted in red to this...

SCR.EntireRow.ClearContents

You did, Rick, and thanks again. Thanks to the other replies also.

 

[Peter_SSs]

You did, Rick, ...

I presume then that in whatever thread that was, it was determined that column G did not contain any existing formulas, otherwise they would be struck-through or cleared as well as the "scr" rows.

 

[Rick Rothstein]

I presume then that in whatever thread that was, it was determined that column G did not contain any existing formulas, otherwise they would be struck-through or cleared as well as the "scr" rows.

If it turned out that there were formulas in Column G mixed in with the "scr" text constants, then we could modify my code approach this way to get around it...

Sub Find_scr_StrikethroughIt()
  Dim SCR As Range
  With Columns("G")
    .Replace "scr", "#N/A", xlWhole
    Set SCR = .SpecialCells(xlCellTypeConstants, xlErrors)
    .Replace "#N/A", "scr", xlWhole
  End With
  SCR.EntireRow.ClearContents
End Sub

Another way to code this (assuming the formulas could be generating errors themselves) would be this way...

Sub Find_scr_StrikethroughIt()
  Dim SCR As Range
  With Columns("G")
    .Replace "scr", "TRUE", xlWhole
    Set SCR = .SpecialCells(xlCellTypeConstants, xlLogical)
    .Replace "TRUE", "scr", xlWhole
  End With
  SCR.EntireRow.ClearContents
End Sub

 

[Haydo]

If it turned out that there were formulas in Column G mixed in with the "scr" text constants, then we could modify my code approach this way to get around it...

Sub Find_scr_StrikethroughIt()
  Dim SCR As Range
  With Columns("G")
    .Replace "scr", "#N/A", xlWhole
    Set SCR = .SpecialCells(xlCellTypeConstants, xlErrors)
    .Replace "#N/A", "scr", xlWhole
  End With
  SCR.EntireRow.ClearContents
End Sub

Another way to code this (assuming the formulas could be generating errors themselves) would be this way...

Sub Find_scr_StrikethroughIt()
  Dim SCR As Range
  With Columns("G")
    .Replace "scr", "TRUE", xlWhole
    Set SCR = .SpecialCells(xlCellTypeConstants, xlLogical)
    .Replace "TRUE", "scr", xlWhole
  End With
  SCR.EntireRow.ClearContents
End Sub

that works very well.
Would it be complicated if i wanted to choose columns B,C and D to be strikethrough, and the remainder to be clear contents?

 

[Rick Rothstein]

Would it be complicated if i wanted to choose columns B,C and D to be strikethrough, and the remainder to be clear contents?

I think this macro will do that for you...

Sub Find_scr_Strikethrough_BCD()
  Dim SCR As Range
  Const LastColumn As String = "J"
  With Columns("G")
  .Replace "scr", "=scr", xlWhole
  Set SCR = .SpecialCells(xlCellTypeFormulas)
  .Replace "=", "", xlPart
  End With
  SCR.EntireRow.Font.Strikethrough = True
  Intersect(Union(Columns("A"), Columns("E:" & LastColumn)), SCR.EntireRow).ClearContents
End Sub