Excel 2007 : Freezing column

**ALEZI** · 07-28-2010, 04:49 AM

Hello,

Could somebody give me solution for case where I have one column with results of calculation, after erasing columns which are part of calculations result should be remain in results column(something like freezing result, progressive entry , see attached file).As I know this possible doing over macro but I’m not familiar with macro, are this possible do in Excel 2007?

Thanks for yours effort...

**Marcol** · 07-28-2010, 05:49 AM

Try this

Sub PreserveResults()
    Dim LastRow As Long
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row

    With Range("C:C")
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With
    Range("A2:B" & LastRow) = 0
    
    Application.CutCopyMode = False
    Range("A2").Select
End Sub

N.B.
with your sample workbook you should unmerge the cells in row 1 first.

Hope this helps

**DonkeyOte** · 07-28-2010, 05:58 AM

@Marcol, I think OP is saying that C should be the Sum of: A, B & prior sum (should it exist).

Of course the issue becomes identifying when C should recalculate (so as to avoid potential for double counting A & B)

**Marcol** · 07-28-2010, 07:34 AM

Maybe this then?

Sub PreserveResults()
    Dim LastRow As Long
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    If Range("F2") = "" Then
        Range("F1") = "Grand Total"
        Range("F2").Formula = "=SUM($D2:E2)"
        Range("F2:F" & LastRow).FillDown
    Else
        Columns("E:E").Insert
    End If
    Range("C2:C" & LastRow).Copy
    Range("E2").PasteSpecial Paste:=xlPasteValues
    
    Range("A2:B" & LastRow) = 0
    
    Application.CutCopyMode = False
    Range("A2").Select
End Sub

Fill in values in Columns A & B then run macro.
Repeat as required.

Hope this helps

**ALEZI** · 07-30-2010, 07:32 AM

Thanks all guys for answers, especially thanks to Marcol, but when I open final version of the document “Preserve results” I got nothing, I leave space than I did something wrong because I’m not so familiar with macro functions, actually my question is how will be activate one macro function as in my example.

Thanks a lot, you are a great.