Copy/paste special values of Excel table based on cell value

[stacey_t]

I'm looking to create a macro that will copy/paste special values (remove formulas) in a table row based on the value in column A of that row being set to "Complete". I'm open to it being a button or automatic but I can't get that far... I tried using the code at this thread Macro to hardcode a row based on formula output but I am getting a Compile error: Argument not optional error.
Sub CopyPasteCompleted() Dim Cl As Range, Rng As Range On Error Resume Next Set Rng = Range("A:A").SpecialCells(xlFormulas) On Error GoTo 0 If Rng Is Nothing Then Exit Sub For Each Cl In Range If Cl.Value = "Complete" Then Cl.EntireRow.Value = Cl.EntireRow.Value End If Next Cl End Sub

 

[Joe4]

There is an error in the code that was pointed out and corrected on the 2nd page of that thread.
This line:

For Each Cl In Range

should be this:

For Each Cl In Rng

 

[stacey_t]

I can't figure out how to edit the original thread. The macro has been edited to

Sub formulatovalue()

ActiveSheet.Select
Dim c As Range
For Each c In Columns(1).SpecialCells(xlConstants)
If c.Value = "Complete " Then
c.EntireRow.Value = c.EntireRow.Value
End If
Next c

MsgBox "Done."

End Sub

Which does work but I'd like to incorporate a HasFormula condition to keep it from looping through every row of the worksheet every time. The last run took a while...