> the spreadsheet  and value out the whole spreadsheet to save
> time.
Do you mean delete as many cell contents as you can?

> was trying to use a macro  that would look for the "X" mark in
> the end of the column
Do you mean an X in a separate column or an X actually at the end of the
contents of a cell like "2345 X"

> also value out the formulas  after it has updated the changes
> to minimize file  space.
&
> in the end column of the report and value them out in the
> end?
Again... What do you mean by value out please?

For row  3... Is there a blank cell at the end? That is, could that be a
signal to stop... Process from col 1 row 3 to col N row 3 till we get to a
blank cell??

For the vertical process... Does the data start  and end at specific rows  or
again... Can we stop if a cell is blank?

Here is what I *think* you mean...
You have...
A set of formulae in row 3.
Beneath each formula you have a set of "vertical" data.
In a separate column at the end of the sheet you have an "X"

You want to...
Loop around row 3 col 1 to row 3 col N till you get to a blank.
Pick up the formula in row 3.
Go down the end column for row 4 to M with the "X"s in it.
For each row with an X in replace the value of the cell at row M col N with
that formula.
For each column without an "X" delete what's in row M col N.

At the end zap the formulas in row 3.

Is that correct please?

I have responded in blue the questions that you had for me below.

Please review and let me know if I have clearly expressed my needs.

So just to be clear....

Is what you want....
Go down a column with Xs in certain cells.
If there is an X in a cell loop  *across* that row  and replace what is in the
cells on that row with whatever formula is in row 3.
After the value has been calculated, replace the formula with the resultant
value.

Is that correct??

... And just to get you going.. The following code will ...
Go down column 6 looking for Xs from row 4 to 15.
When it gets an X go across that row from column 1 to 5.
Pick up the formula from row 3 for that column.
Put it in that row/column.
Copy the cell and paste special it back.

Be aware that the start  end values are all hard  coded.
Given a bit more time  I'd collect all the row 3 formulae in an array so that
I didn't have to look them up all the time.

Sub subValueOut()

Dim rlCurrentRow3Cell As Range
Dim rlCurrentColumnCell As Range
Dim rlCurrentXCell As Range
Dim llColumn As Long
Dim llRow As Long
Dim slFormula As String
Dim llColumnWithXes As Long
Dim llDataStartRow As Long
Dim llDataEndRow As Long
Dim llDataStartColumn As Long
Dim llDataEndColumn As Long

' Set initial values.
slFormula = ""
llColumnWithXes = 6
llDataStartRow = 4
llDataEndRow = 15
llDataEndColumn = 5
llDataStartColumn = 1

' Go DOWN the X column.
For llRow = llDataStartRow To llDataEndRow

Set rlCurrentXCell = Cells(llRow, llColumnWithXes)
If UCase(rlCurrentXCell.Value) = "X" Then

' Got an X.
' Go ACROSS.
For llColumn = llDataStartColumn To llDataEndColumn

Set rlCurrentColumnCell = Cells(llRow, llColumn)
Set rlCurrentRow3Cell = Cells(3, llColumn)

slFormula = rlCurrentRow3Cell.Formula

' Replace with formula from Row 3.
rlCurrentColumnCell.Select
rlCurrentColumnCell.Formula = slFormula

DoEvents

' Copy and pastes special.
rlCurrentColumnCell.Copy
rlCurrentColumnCell.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

Next llColumn

Else

' Skip this row if no X.

End If
Next llRow
MsgBox "Done."
End Sub

It is pretty impressive what you do.
I'll follow your instructions carefully and let you know if everything went
through ok.

Again, many thanks for your support and talk to you soon.

It's pretty exciting to see your macro  coming alive in Excel. It is
exactly what I was looking for.

However, is it possible if you can help make one adjustment witht he macro?

When the macro replaces the row  that has an X with the formulas  in row 3 (in
current case row 5), the formula doesn't flow instead it pastes the same source
in row 5.

Example:

When I drag dow a formula from cell A5 to cell A6 below the formula adjust to
the conditions of cell A6. So when a formula in A5 is = B5+C5 and it is dragged
to cell A6 it changes to =B6+C6 and so on. I would like to have this function
applied in my macro.

Right now when I run the macro it doesn't adjust to the following cell instead
it's looked to cell A5. So any cell down the row A6, A7, A9, A12, etc, have the
same formula from as cell A5. Can you please help me adjust this macro so when
it is replaced by the formula down the rows  it adjusts accordingly?

Hey I can thank you enough for your help.

Here is how the current macro loolks for your reference.

Sub UltimatesMacro()

Dim rlCurrentRow10Cell As Range
Dim rlCurrentColumnCell As Range
Dim rlCurrentXCell As Range
Dim llColumn As Long
Dim llRow As Long
Dim slFormula As String
Dim llColumnWithXes As Long
Dim llDataStartRow As Long
Dim llDataEndRow As Long
Dim llDataStartColumn As Long
Dim llDataEndColumn As Long

' Set initial values.
slFormula = ""
llColumnWithXes = 1
llDataStartRow = 10
llDataEndRow = 1500
llDataEndColumn = 84
llDataStartColumn = 2

' Go DOWN the X column.
For llRow = llDataStartRow To llDataEndRow
Set rlCurrentXCell = Cells(llRow, llColumnWithXes)
If UCase(rlCurrentXCell.Value) = "X" Then

' Got an X.
' Go ACROSS.
For llColumn = llDataStartColumn To llDataEndColumn
Set rlCurrentColumnCell = Cells(llRow, llColumn)
Set rlCurrentRow10Cell = Cells(10, llColumn)
slFormula = rlCurrentRow10Cell.Formula

' Replace with formula from Row 10.
rlCurrentColumnCell.Select
rlCurrentColumnCell.Formula = slFormula

DoEvents
' Copy and pastes special.
rlCurrentColumnCell.Copy
rlCurrentColumnCell.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

Next llColumn

Else

' Skip this row if no X.
End If
Next llRow
MsgBox "Done."

Range("A1").Select
Sheets("NU").Select
Application.CutCopyMode = False

End Sub

Ok... I'm working on it but a bit busy at the mo... It may be as late as
tommorow before I get back.

You're introducing a whole new language to me!!! "value out" and "flow".

Ok... I had a bit of time  so I looked at the code again.

The following will I think do what you want.

I've made 2 changes.
1) Instead of pasting the formula in I've *copied* it from row  3. That means
the formula keeps the relative references and "flows". Hehehe.
2) I've left the copy  and paste special till the end and done it for the
whole data area instead of one cell at a time.

If anything it should go a little faster now!

Let me know if this isn't what you want Jae.


Sub subValueOut()

Dim rlCurrentRow3Cell As Range
Dim rlCurrentColumnCell As Range
Dim rlCurrentXCell As Range
Dim rlAllData As Range
Dim llColumn As Long
Dim llRow As Long
Dim slFormula As String
Dim llColumnWithXes As Long
Dim llDataStartRow As Long
Dim llDataEndRow As Long
Dim llDataStartColumn As Long
Dim llDataEndColumn As Long

' Set initial values.
slFormula = ""
llColumnWithXes = 6
llDataStartRow = 4
llDataEndRow = 15
llDataEndColumn = 5
llDataStartColumn = 1

' Go DOWN the X column.
For llRow = llDataStartRow To llDataEndRow

Set rlCurrentXCell = Cells(llRow, llColumnWithXes)
If UCase(rlCurrentXCell.Value) = "X" Then

' Got an X.
' Go ACROSS.
For llColumn = llDataStartColumn To llDataEndColumn

Set rlCurrentColumnCell = Cells(llRow, llColumn)
Set rlCurrentRow3Cell = Cells(3, llColumn)

' Copy cell in row 3 so that it "flows".
rlCurrentRow3Cell.Copy
rlCurrentColumnCell.Activate
ActiveSheet.Paste

DoEvents

Next llColumn

Else

' Skip this row if no X.

End If
Next llRow

' Done with formulae.

' Copy paste the whole data area.
Set rlAllData = Range( _
Cells(llDataStartRow, llDataStartColumn), _
Cells(llDataEndRow, llDataEndColumn) _
)
rlAllData.Copy
rlAllData.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False


MsgBox "Done."
End Sub

Unbelievable. It works perfectly!!!! You are amazing!!!!
You might not know how much this means to me and my department. We are all so
excited here at Disney Finance thanks to you.

I hope I can be even half as good as you someday. How do you guys at
YahooExcelVBA group do this? Do you guys take classes? Or you guys are self
taught. Either way what you guys do is very impressive. Thanks for just being
there for me and others who need help.

Again thank you so much and definitely I'll keep in touch with you.