How can display multiple values to single column?

Vba - dynamic range, copy and 'paste special' values only

• QUESTION: I have a workbook with a huge number of array formulae, which will have up to a couple of hundred columns with as many rows as there are school days in the year, which will soon go over thelimit on array formulae (certainly in2002), and slows the spreadsheet down. My dates start at R8C1 and I will have a number of columns (not yet finalised, but that will stay fixed over time). All cells in columns to the right of the dates have very long array formulae entered I would like to find a way to search the array of data that is less than today's date, and find the last row in which there is any value above 0, and then to copy all rows above and including that row, then paste special their values only, into exactly the same location we just copied them from. I strikes me this should be pretty simple to someone who knows what they're doing judging by a simple copy and past macro I recorded, but I don't know anywhere near enough to take it beyond this. Sub CopyPasteValues() ' ' CopyPasteValues Macro Range("B8:DA126") End Sub Once I have this I think I can probably crack having the macro drag down the array formulae X rows into the future, so that we never have more than 20-30 rows or so of array formulae, which is the end goal. Hope that makes sense and ANSWER: Peter, test this on a copy of your workbook. It worked fine for me in the bit of testing I did, but my sheet had no merged cells and none of the formulas were producing error values. You should have no merged cells either. It can be adjusted for error values if they are problematic. Sub ChangeFormulaResultstoConstants() Dim r As Range, cell As Range, r1 As Range, r2 As Range Dim rw As Long, cell1 As Range Dim calc As Long ' store current calculation mode setting calc = Application.Calculation ' set calculation to manual Application.Calculation = xlCalculationManual With ActiveSheet Set r = .Range(.Cells(8, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With For Each cell In r If IsDate(cell) Then If cell ' now find all the formulas that produce numbers in that range On Error Resume Next Set r2 = r.SpecialCells(xlFormulas, xlNumbers) On Error GoTo 0 rw = 8 If Not r2 Is Nothing Then For Each cell In r2 If cell.Value <> 0 Then If cell.Row > rw Then rw = cell.Row End If Next End If If rw = 8 Then Exit Sub ' rw now holds the last row that contains a non-zero value and date less than today Set r = r.Resize(rw - 7, r.Columns.Count) ' now replace all the formulas in the range with the values they display r.Formula = r.Value ' restore calculation setting Application.Calculation = calc End Sub ---------- FOLLOW-UP ---------- QUESTION: That's absolutely perfect! At first it was pasting everything up to today's date, but that prompted me to check then tweak my array formulas as they were mostly not returning numeric values but text values instead and it now seems to work perfectly. I don't know how easy this would be - I've tried to see if I could infer it from your code but I realise I'm completely out of my depth having only some basic VBScript knowledge and no VBA! Thinking about how this spreadsheet will be administered, I've realised I need a macro to: 1) Find the last row containing any information 2) Drag those formulas down(including column 1 which contains dates) up to, say, 1 week beyond today's date (to ensure that some array formulas always remain and we don't accidentally paste values over all of them) 3) THEN perform the copy and paste your code already achieves so perfectly Is that something that could be added to the beginning of the macro without too much extra work for yourself? Peter

• Answer:

  Peter, I added code to extend the formulas down until the last date is 7 days after the current date. Sub ChangeFormulaResultstoConstants() Dim r As Range, cell As Range, r1 As Range, r2 As Range Dim rw As Long, cell1 As Range, r3 As Range Dim calc As Long, dt As Date, cnt As Long ' store current calculation mode setting calc = Application.Calculation ' set calculation to manual Application.Calculation = xlCalculationManual With ActiveSheet Set r = .Range(.Cells(8, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With For Each cell In r If IsDate(cell) Then If cell '--------------- ' code added to fill down to reach 7 days after today With ActiveSheet Set r3 = .Cells(.Rows.Count, 1).End(xlUp) dt = Date + 7 cnt = dt - r3 If cnt > 0 Then Set r3 = r3.Resize(1, r1.Column) r3.AutoFill Destination:=r3.Resize(cnt + 1, r3.Columns.Count), Type:=xlFillDefault End If End With '--------------- ' now find all the formulas that produce numbers in that range On Error Resume Next Set r2 = r.SpecialCells(xlFormulas, xlNumbers) On Error GoTo 0 rw = 8 If Not r2 Is Nothing Then For Each cell In r2 If cell.Value <> 0 Then If cell.Row > rw Then rw = cell.Row End If Next End If If rw = 8 Then Application.Calculation = calc Exit Sub End If ' rw now holds the last row that contains a non-zero value and date less than today Set r = r.Resize(rw - 7, r.Columns.Count) ' now replace all the formulas in the range with the values they display r.Formula = r.Value ' restore calculation setting Application.Calculation = calc End Sub