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?

 

[wigi]

So, you meant to use an Autofilter, filter on columns C and D for the value of 3 and then turn the formulas into values. That would not be a lot of work manually (even 5000 rows are done quickly with an autofilter).

 

[boznian]

So, you meant to use an Autofilter, filter on columns C and D for the value of 3 and then turn the formulas into values. That would not be a lot of work manually (even 5000 rows are done quickly with an autofilter).

The problem with doing that however is that my script does not work using the autofilter if I select multiple rows that have non-desireable rows (rows that don't match my criteria, hidden by autofilter) in between. Which means I have to manually select only contiguous rows - very time-consuming and fully interactive.

 

[p45cal]

You might have a little difficulty manually cutting and pasting Special|Values to a filtered list, as it might complain that destination range is not same size as source.
A quicker way to convert formulae to values than your code is with the likes of:
Range("A1").value = Range("A1").value

Try this, which works on the rows which are selected (you don't have to select the whole rows, any single column of cells will do). You'll have to adjust the
Range("D:D,F:F,I:I,K:M")
to match the non-contiguous columns you want to convert to values.

Sub Macro10()
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, [COLOR=Red]Range("D:D,F:G,I:I,K:M")[/COLOR]).Areas
            ar.Value = ar.Value
        Next ar
    End If
Next rw
End Sub

 

[wigi]

The point is that you do not have to select rows. Or is that an explicit requirement? (why, would I then ask)

If you do not need to select, this will work if your table starts in cell A1 and is well-behaved.

Sub PasteValuesOfFilteredRange()

    With Range("A1").CurrentRegion
        .AutoFilter 3, 3
        .AutoFilter 4, 3
        .Offset(1, 2).Resize(, 2).SpecialCells(xlCellTypeVisible).Value = 3
        .AutoFilter
    End With

End Sub

It converts only columns C and D if they are equal to 3.

 

[boznian]

"A quicker way to convert formulae to values than your code is with the likes of:
Range("A1").value = Range("A1").value"

I have been trying to implement this with no success - Method 'Range of Object'_Global Failed. When I click on help nothing but a blank help screen comes up. Here is what I have been trying:

     crow = ActiveCell.Row
    Range(crow, "C:C").Value = Range(crow, "C:C").Value

because obviously the row has to be a variable...

As for "Try this, which works on the rows which are selected (you don't have to select the whole rows, any single column of cells will do). " The problem is that I need it to be brainless for the operators. This operation has to be done on a regular basis, at the touch of a button. So, if I can find a way to just have the code search for and execute on any row that has 3 and 3 in C and D, then exit when it finds a blank cell in Col A, I'll be all set.

 

[p45cal]

Range(crow, "C").Value = Range(crow, "C").Value
but this is only a single cell, column C of the active cell's row.

Back to the script:
If you change the line
For Each rw In Selection.EntireRow.Rows
to
For Each rw In Activesheet.usedrange.entirerow.Rows
it will process the whole sheet's used range.
I don't know if there are different regions in your sheet doing different jobs, nor do I know which row to start from (row 1?). So if your sheet is just one big table this will work.

 

[boznian]

Range(crow, "C").Value = Range(crow, "C").Value
but this is only a single cell, column C of the active cell's row.

For some reason I still get the same error - maybe because I am using 2003? I don't see anything that I am doing wrong here:

Sub Macro98()

    crow = ActiveCell.Row
    Range(crow, "C").Value = Range(crow, "C").Value

End Sub

I hate to continue with the full script until I get this worked out because you said that it is "quicker". Right now it takes about 8 seconds per row if I do one row at a time, but for some reason seems to take much longer per row if multiple rows are selected...

 

[p45cal]

my script in message #4 uses the quicker method (ar.value=ar.value - no copy/pasting values there).

re:
Range(crow, "C").Value = Range(crow, "C").Value
I have to apologise, that should say:
Cells(crow, "C").Value = Cells(crow, "C").Value
but don't use it.

 

[p45cal]

it takes about 8 seconds per row if I do one row at a time

!!!

Could you list out the column letters which you want to convert to values in each row.