Excel crash when attempting to delete last remaining column of formulas [?!]

Biogeneric

New Member
Joined
Nov 30, 2016
Messages
5
Hi guys,

Got quite a unique issue here, and a solution would mean the world to me as it's for a high-priority project at my place of work.

So I consider myself to be quite proficient at Excel and VBA, having used it on a daily basis for the past few years for countless different tasks, however this issue has me and my colleagues stumped.

Here's the issue...
So I have around 30 columns, each with ~100,000 rows of data (many blank cells though I might add). Several of these columns have formulas added to them, inserted via a VBA module (see detail of these formulas below, consisting of IFs and VLOOKUPs).

Now, loading these formulas, and calculating the sheet, actually runs quite quickly, surprisingly. The issue is, in the interest of spreadsheet usability, I need to convert ALL of these formulas to values. When I try and do this via VBA, it crashes (stops responding for what appears to be forever).

Now, if I manually try and delete the cell contents of each of these columns, one by one, it deletes almost instantly UNTIL I reach the last remaining column with formulas in, and that one crashes (running out of memory?). Even using a small sample of 100 rows takes an absolute age.

What's weird is, it doesn't matter what column, it's always the last one I get to (whether it's column A, B, C etc.) that causes these memory issues. All of the other columns clear almost instantly.

What I have tried...
I've tried the attempted solutions as above, I've tried running in safe mode, I've tried repairing the file in case of some form of corruption, I've tried writing a code that steps through a loop and removes the formulas line by line, I've tried removing ALL formatting from the sheet - none of these things have worked.

I'm at the point now where I don't know what to do.

Snippet from my code that inserts the formulas based on criteria...
Code:
For j = 1 To endColTrk
            If shtTrk.Cells(i, j).Value <> shtTrk.Range("G" & i).Value Then
                If Len(shtTrk.Cells(i, j).Value) > 2 Then
                    formLen = (Len(shtTrk.Cells(i, j).Value) + 1) / 3
                    Select Case formLen
                        Case 2
                         shtTrk.Range(shtTrk.Cells(midRow, j).Address, shtTrk.Cells(endRowTrk, j).Address).Formula = _
                          "=IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE),""""))"
                        Case 3
                         shtTrk.Range(shtTrk.Cells(midRow, j).Address, shtTrk.Cells(endRowTrk, j).Address).Formula = _
                          "=IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE),"""")))"
                        Case 4
                         shtTrk.Range(shtTrk.Cells(midRow, j).Address, shtTrk.Cells(endRowTrk, j).Address).Formula = _
                          "=IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE),""""))))"
                        Case 5
                         shtTrk.Range(shtTrk.Cells(midRow, j).Address, shtTrk.Cells(endRowTrk, j).Address).Formula = _
                          "=IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE),"""")))))"
                        Case 6
                         shtTrk.Range(shtTrk.Cells(midRow, j).Address, shtTrk.Cells(endRowTrk, j).Address).Formula = _
                          "=IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 5) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 5) * 3) - 2, 2) & ",FALSE),""""))))))"
                        Case 7
                         shtTrk.Range(shtTrk.Cells(midRow, j).Address, shtTrk.Cells(endRowTrk, j).Address).Formula = _
                          "=IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 5) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 5) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 6) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 6) * 3) - 2, 2) & ",FALSE),"""")))))))"
                        Case 8
                         shtTrk.Range(shtTrk.Cells(midRow, j).Address, shtTrk.Cells(endRowTrk, j).Address).Formula = _
                          "=IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 5) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 5) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 6) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 6) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 7) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 7) * 3) - 2, 2) & ",FALSE),""""))))))))"
                        Case 9
                         shtTrk.Range(shtTrk.Cells(midRow, j).Address, shtTrk.Cells(endRowTrk, j).Address).Formula = _
                          "=IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 5) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 5) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 6) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 6) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 7) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 7) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 8) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 8) * 3) - 2, 2) & ",FALSE),"""")))))))))"
                        Case 10
                         shtTrk.Range(shtTrk.Cells(midRow, j).Address, shtTrk.Cells(endRowTrk, j).Address).Formula = _
                          "=IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 0) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 1) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 2) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 3) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 4) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 5) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 5) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 6) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 6) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 7) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 7) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 8) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 8) * 3) - 2, 2) & ",FALSE)" & _
                          ",IF(VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 9) * 3) - 2, 2) & ",FALSE)<>"""",VLOOKUP($A" & midRow & ",'LLUMS " & curFunc & "'!$A4:$BZ" & endRowCur & "," & Mid(shtTrk.Cells(i, j).Value, ((formLen - 9) * 3) - 2, 2) & ",FALSE),""""))))))))))"
                    End Select
                End If
            End If
        Next

I originally tried to achieve my desired outcome with several loops, in replacement of the lookups, rather than formulas, however this ended up taking hours (understandably). Formula based seemed the best solution, which it is, all up until I need to convert the last column to values.

Anyone come across anything like this before? Is there even a solution to this?

Any help would be greatly appreciated.

Thank you in advance,
Jack
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Hi:

I am not sure if it would work for you or not but what about changing the entire sheet to values using a different macro? You can call the macro below using the command:

Code:
Call All_Cells_In_Active_Sheet_To_Values

Code:
All_Cells_In_Active_Sheet_To_Values()
    With ActiveSheet.UsedRange
        .Cells.Copy
        .Cells.PasteSpecial xlPasteValues
        .Cells(1).Select
    End With
    Application.CutCopyMode = False
End Sub

Not sure if that will work for you or not. Make sure if you TRY it you do so on a test copy of your spreadsheet.

Good Luck,
Mark
 
Upvote 0
Thanks for your response, Mister H.

Unfortunately I've already tried doing this in a separate module and it still locks up.

It's almost as if, every cell which has a formula in that I remove, the memory that it took up then moves onto the next cell, and so on, until the last column is just overloaded and can't be wiped. Weird right?
 
Upvote 0
Weird Indeed :) I tred to PM you but it says your mailbox is to full and you need to delete some items before you can receive anymore messages. Anyway, all I was going to say is that I cant recreate your error but would be wiling to have a look at a non confidential copy of your sheet to see if I can assist you. If that is not doable then hopefully a Excel Guru will jump in an aid you.
 
Upvote 0
Weird Indeed :) I tred to PM you but it says your mailbox is to full and you need to delete some items before you can receive anymore messages. Anyway, all I was going to say is that I cant recreate your error but would be wiling to have a look at a non confidential copy of your sheet to see if I can assist you. If that is not doable then hopefully a Excel Guru will jump in an aid you.

Strange, I only have one PM in there! Thanks for your offer, really appreciate that. I'll see if I can work on a non confidential copy tomorrow and send it over to you, thanks so much!
 
Upvote 0
What happens when you copy all or part of the sheet and paste in place "As Values"? Does this also "error out"?
 
Upvote 0
Just to add to this, we've tested this on 3 different PCs now, each with varying RAM (up to 16GB, which I believe he has 64 bit Excel also to utilise) and it's the same issue on each.

The only difference is, on mine, it just locks up completely with no warning or error, on the other two, it comes up with that "not enough resources available" error message.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top