excelvbareplacenonblank

How do I remove zero-length strings in Excel using VBA?

I've created a tool in VBA for Excel that analyses .xlsx files we receive based on a number of criteria. One of those is the number of empty cells in the dataset. Unfortunately, though, I have noticed that a number of the files we receive contain cells with zero-length-strings in them which are being "incorrectly" counted as non-empty cells.

It's actually highly beneficial to the whole process if I am just able to remove these from the file.

I have googled this problem a lot, but the only solution I have been able to find so far is to loop through all cells in the sheet (I've also tried just constants, and also using Find to find all zls). This isn't very efficient as the worksheets have large amounts of data in them. EDIT: I have also tried the UsedRange.values = UsedRange.values method, but this removes leading zeroes, which I require.

I have also discovered that this works (-@- is a random string that is highly unlikely to be a single cell in my data, and if it's there, is fine to be removed):

ws.UsedRange.Replace what:=vbNullString, replacement:="-@-", _
                            lookat:=xlWhole, MatchCase:=False
ws.UsedRange.Replace what:="-@-", replacement:="", _
                            lookat:=xlWhole, MatchCase:=False

but if I only do it using the single replace it doesn't:

ws.UsedRange.Replace what:=vbNullString, replacement:="", _
                            lookat:=xlWhole, MatchCase:=False

The first is ok, but has a number of problems:

  1. It takes twice as long as a single replace
  2. It's not always clear if it has crashed, or continued to work
  3. If it does crash, I am left with a number of cells containing -@-, which isn't always obvious, and the tool should be ok to be used by someone who isn't able to understand VBA.

So my questions:

  1. Is there a way of doing this using just a single replace? Why does replace work with the double replace, but not the single?
  2. If the first isn't possible, then is there a way of 'rolling back' the replacement if the code crashes?
  3. Is there a way of updating the status bar to show how far through the replace is to prove the code is running (much like when running replace in Excel itself)?
  4. Or is there just a better way of doing it all?

Thanks in advance!

EDIT: Unfortunately, due to the data being processed, I need to retain formatting, including leading zeroes

EDIT: Here is an example of the sort of data I am looking at. I want to replace the zero-length-strings (which are non-blank cells that have no value in them) so they are a truly blank cell.

Table containing zero-length-strings

Solution

  • I know this is a loop as well but maybe a quicker way:

    Before:

    enter image description here

    Run this code on sample data:

    Dim X As Double

Option Explicit

Sub Test()

Application.ScreenUpdating = False
Application.Calculation = xlManual

With ActiveWorkbook.Sheets(1).Range("A1:C7")
    For X = 1 To 3
        .AutoFilter Field:=X, Criteria1:=""
        .Columns(X).Offset(1, 0).SpecialCells(xlCellTypeVisible).Clear
    Next X
    .AutoFilter
End With

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

End Sub

    After:

    enter image description here