Help w/ automating a script based on value

[boznian]

I really don't know where to begin with this one. I am trying to reduce the overhead of unnecessary formula's in my spreadsheet for items (rows) that have long since been sold and no longer need to be re-calculated every time by replacing the formula with its text (numerical) result . So I am doing this:

    crow = ActiveCell.Row
    Application.ScreenUpdating = False
    Cells(crow, "C:C").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'    Repeats 15 times for each row, using different column values
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

The problem is that I have to select each row manually (have ~ 5,000 rows to go through and the rows I need aren't necessarily contiguous) based on criteria in 2 columns.

Values in column C & D are both the result of formula's (if that makes a difference) and if the results of the formula in both Col C & D in the current row are "3" (either column might be blank, 0, 1, 2 or 3), then my script to replace the formula's needs to run on that row. If anything other than 3 & 3 in col C & D, then skip the row and evaluate the next, repeating until a blank row (Column A is blank) is found.

Can anyone help me with this?

 

[boznian]

Sorry for the delay, work and stuff...

OK, so this is what I have tried and I still get the same error as above:
"Method 'Range of Object'_Global Failed"

For Each rw In Selection.EntireRow.Rows
    If Cells(rw.Row, "C") = 3 And Cells(rw.Row, "D") = 3 Then
        For Each ar In Intersect(rw, Range("C:C,D:D,K:K,L:L,U:U,X:X,AA:AA,AC:AC,AG:AG,AH,AH,AI:AI,AJ:AJ,AK:AK,AL:AL")).Areas
            ar.Value = ar.Value
        Next ar
    End If
Next rw

Tried selecting a single row, multiple rows, leaving out cols. C and D, tried changing "Range" to "Cells", etc., but I cannot get it to run?

 

[boznian]

Oh jeeezz - stupid mistake on my part, "...AG:AG,AH,AH,AI:AI," should have been "AH:AH" of course! Just tripped me up getting the same error message as before.

What a timesaver! Thanks p45cal!

 

[wigi]

Didn't you notice that you have AH,AH instead of AH:AH?

In addition, why not using:

Sub tst()
    For Each rw In Selection.EntireRow.Rows
        If Cells(rw.Row, "C") = 3 And Cells(rw.Row, "D") = 3 Then
            For Each ar In Intersect(rw, Range("C:D,K:L,U:U,X:X,AA:AA,AC:AC,AG:AL")).Areas
                ar.Value = ar.Value
            Next
        End If
    Next
End Sub