Ghost values in the cells

Question

I seem to have a strange problem as some ghost values have entered my file. I got this file from someone but looks like file has seen several deletion, copy pastes etc. Please see attached image.

It shows ghost values in cell J186 and the values returned by various IS*** functions on cell j186. Such values are there in several columns in the file and I am sure they are consuming a lot of Filesize and the file is crashing every now and then. The file is 100 MB.

For example, when I select any cell in column L say Cell L56 and press Ctrl+Down, the cursor gets stuck in the cell L186 even when there is no value. If I select the cells L3:L186 and manually enter delete, something gets deleted (I cant see) and then the range functions as a normal range (i.e. If i select any random cell in that range and do a Ctrl+Down, it goes to the last row in Excel Row 1048576) Any cell in the range upto L186 shows the same behaviour as cell J186.

Is there a way to write a VBA code to identify such cells and clear contents of such cells? Thanks in advance.

Answer 1

Yes, there is something strange here ..... zero length cells that are not actually blank (when tested with SpecialCells(xlBlank)

On your sample file =CODE(A117) returns #VALUE. Yet the cell is not blank

This array based code provides a very quick way of turning the cells to truely blank

Sub QuickReplace()
Dim rng1 As Range
Dim X
Dim lngRow As Long
Dim lngCol As Long

ActiveSheet.UsedRange

X = ActiveSheet.UsedRange.Value2
For lngRow = 1 To UBound(X, 1)
    For lngCol = 1 To UBound(X, 2)
        If Len(X(lngRow, lngCol)) = 0 Then X(lngRow, lngCol) = vbNullString
    Next
Next
ActiveSheet.UsedRange.Value2 = X

End Sub

Answer 2

This code was successful. But the file size did not decrease much.

Sub cleancolumns()
Dim i As Integer
Dim j As Integer
Dim Rng As Range

j = 1

Do While j < 5010
Set Rng = Range(Cells(5, j), Cells(186, j))
If WorksheetFunction.Sum(Rng) = 0 Then
    Rng.Select
    Selection.ClearContents
    j = j + 1
Else
    j = j + 1
End If
Loop

ActiveWorkbook.Save

End Sub

There are a million cells in all, also counting the above ranges where full range is "". searching each cell one by one is very slow. Hence I did the above workaround.

The above code checks for the sum of the range and if the sum of the range is zero it is assumed to contain ""'s and clears contents. Else it skips the column and checks for the next column.

However, this does not remedy a situation where there are few genuine values and the rest are ""'s. These also have to be taken into account in a separate If statement i guess. That will make it very slow but doing this appears to be unavoidable.

---

Update based on Brettdj's response

---

The following variant of Brettdj's code worked. usedrange appeared to be larger than what my 6GB computer could handle. So I broke the data chunk by chunk to avoid "Out of memory" error. Also there were some error values which had to be removed before the Len function was applied. Now the file sizes have shrunk by a third (mainly by replacing 0's by blanks - there were too many). Thankfully the ghosts seem to have been busted.

Sub QuickReplace1()
Dim rng1 As Range
Dim X As Variant
Dim lngRow As Long
Dim lngCol As Long

' took no more than 500 columns at a time not to risk file crashing. Changed the values      manually to clear chunk by chunk
Set rng1 = Range(Cells(1, 3501), Cells(7500, 4000))

X = rng1.Value2
For lngRow = 1 To UBound(X, 1)
    For lngCol = 1 To UBound(X, 2)
        If IsError(X(lngRow, lngCol)) Then X(lngRow, lngCol) = vbNullString
        If X(lngRow, lngCol) = 0 Then X(lngRow, lngCol) = vbNullString
        If Len(X(lngRow, lngCol)) = 0 Then X(lngRow, lngCol) = vbNullString
    Next
Next

rng1.Value2 = X

End Sub