Value out row based on a cell value

[goondini]

Hi Guys,

I'm trying to create a macro that will value a line once a specific cell has been been updated. I believe this should be easy for you.

A little context
We are tracking shipments of widgets on our Working Sheet. The Working Sheet references a Data Dump tab with xlookups and sumifs. Once a shipment arrives, we indicate this by Updating 'WorkingSheet'B:B with an "x". After arrives, we no longer need the references, it can be valued out.

If a shipment has not arrived, B:B will be blank. If B:B has an X, we need to value the line out.

I can use go to special function and look for constants. This will select any cells in B:B that have an "X". How can I then write a script to value out any rows that are currently selected?

The following will select any cells with "x"


Range("B2").Select
Application.Goto Reference:="R50000C2"
Range(Selection, Cells(1)).Select
Range("B2:B50000").Select
Range("B50000").Activate
Selection.SpecialCells(xlCellTypeConstants, 23).Select

[p45cal]

1 to 6 above can be condesed to:
Range("B2:B50000").SpecialCells(xlCellTypeConstants, 23).Select
but now I don't know what to 'value out' means.

[goondini]

but now I don't know what to 'value out' means.

Value out the formulas in the selected range. Copy, paste as values.

[Aussiebear]

I'm trying to create a macro that will value a line once a specific cell has been been updated.

The above statement could mean anything...



We are tracking shipments of widgets on our Working Sheet. The Working Sheet references a Data Dump tab with xlookups and sumifs. Once a shipment arrives, we indicate this by Updating 'WorkingSheet'B:B with an "x". After arrives, we no longer need the references, it can be valued out.

The term "valued out" is misleading here since you indicate that the row is no longer needed. Why not simply delete the row once an "X" is placed in the Cell B of the active row?

[p45cal]

For Each are In Range("B2:B50000").SpecialCells(xlCellTypeConstants, 23).Areas
are.Value = are.Value
Next are

[p45cal]

What's nonsensical here is that SpecialCells(xlCellTypeConstants, 23) only returns cells that already have plain values in them.