Replies to post #33214 on A.I.M. Users Bulletin Board (AIMUSERS)

[OLD NO.7]

OH That's what you want to do. You want those rows to stay put while you scrol down. In Excel you go to View and then choose freeze panes then choose freeze top row. I don't know off hand how to freeze the top two rows.

Larry G

[OLD NO.7]

OK here is how you do the top two rows. Put you curser on row 3 way on the left and click which will highlight all of row 3. Now go to view and then click on freeze panes. That should do it. It did it for me Excel 2007.

Larry G

[ls7550]

I have not used Excel Macro’s for about 10 years or more, so I had completely forgotten about that possibility

 
 Private Sub Workbook_Open() 
  
   startrow = 10  ' assumes your dates start in row 10 
   ' older dates 
   i = startrow 
   While Range("A" + CStr(i)) <= Range("a1")  
   ' assumes =today() i.e. current date is in cell a1 
     i = i + 1 
   Wend 
   ' this selects columns B through Z as the range 
   r = "b" + CStr(startrow) + ":z" + CStr(i) 
   Range(r).Copy 
   Range(r).Select 
   Selection.PasteSpecial Paste:=xlPasteValues 
   Range("a1").Select 
   Application.CutCopyMode = False 
  
 End Sub 
 

[OLD NO.7]

Conrad

Again another post on a problem you already solved. I guess I had a difficult time understanding what it was you wanted to do and jumped to the wrong conclusion. Below is what Excel says for replacing a cells formula with the numerical results of the cell. I think as Clive pointed out it is just a matter of cut and paste but paste special (values only). Glad you solved the problem.


Replace formulas with their calculated values
When you replace formulas with their values, Microsoft Office Excel permanently removes the formulas. If you accidentally replace a formula with a value and want to restore the formula, click Undo immediately after you enter or paste the value.

1.Select the cell or range of cells that contains the formulas.
If the formula is an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.), select the range that contains the array formula.

How to select a range that contains the array formula
Click a cell in the array formula.On the Home tab, in the Editing group, click Find & Select, and then click Go To.Click Special.Click Current array.
2.Click Copy .
3.Click Paste .
4.Click the arrow next to Paste Options , and then click Values Only.
The following example shows a formula in cell D2 that multiplies cells A2, B2, and a discount derived from C2 to calculate an invoice amount for a sale. To copy the actual value instead of the formula from the cell to another worksheet or workbook, you can convert the formula in its cell to its value by doing the following:

1.Press F2 to edit the cell.
2.Press F9, and then press ENTER.


After you convert the cell from a formula to a value, the value appears as 1932.322 in the formula bar. Note that 1932.322 is the actual calculated value, and 1932.32 is the value displayed in the cell in a currency format.



Tip When you are editing a cell that contains a formula, you can press F9 to permanently replace the formula with its calculated value.

Larry G