Hi Blanka

Try this

Sub ClearEmptyCells1()
' Processes an entire worksheet
' Targets all cells containing constants and text
' If those cells contain only spaces, then the cells are cleared

Dim TextCells As Range
Dim c As Range
Dim iCnt As Long
Dim sTemp As String

' Set the range of cells to check: Only constants and text values
Set TextCells = Cells.SpecialCells(xlCellTypeConstants, xlTextValues)

For Each c In TextCells
If Trim(c.Value) = "" Then
' Clear just the contents of the cell
If c.MergeCells Then
c.MergeArea.ClearContents
Else
c.ClearContents
End If
iCnt = iCnt + 1
End If
Next c

sTemp = "Cleared " & iCnt & " cell"
If iCnt <> 1 Then sTemp = sTemp & "s"
MsgBox sTemp
End Sub

Sunny

You could use:

c.value = vbnullstring

which will work with merged cells too.

Thank you Sunny so much. This works perfectly.

Velouria - thank you for your answer also. I wonder what the difference is between the two solutions. I added a MsgBox showing me the number of cells cleared and ran Sunny's code. According to the message box, 29 cells have been cleared. Immediately after that I ran the code again and message box shows 0 cells cleared, so this tells me that the first round of code did indeed clear all the empty cells.

I closed and reopened the file without saving and ran your code. Message box showed 29 cells cleared. I ran your code immediately after that and the message box showed 29 cells cleared again. Ran it again, and got the 29 cells again.

Thank you,

Blanka

I can't reproduce that. I get a number the first time and then 0 thereafter. What exact code did you use?

Velouria,

I used the following:

Sub ClearEmptyCells1()
    
    Dim TextCells As Range
    Dim c As Range
    Dim iCnt As Long
    Dim sTemp As String
   
    ' Set the range of cells to check: Only constants and text values
    Set TextCells = Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
   
   
   
    For Each c In TextCells
        If Trim(c.Value) = "" Then

               c.Value = vbNullString

            iCnt = iCnt + 1
        End If
    Next c
   
   
    sTemp = "Cleared " & iCnt & " cell"
    If iCnt <> 1 Then sTemp = sTemp & "s"
    MsgBox sTemp

End Sub

Did I do it incorrectly?

Thank you.

Hi Blanka

I too am unable to replicate your result.

I am getting the same result as Velouria. 1st time there is a number, 2nd time is 0.

Can't be sure of the reason. Possibly your data.

Are you able to post your file for us to have a look?

Sunny

Hi Sunny,

Here is the file. I replaced only the actual data with fake values. The rest is untouched.

When I first run it with c.Value = vbNullString, I get 32 cleared cells, then in subsequent tries I keep getting 29 cleared cells.

At this point I'm very curious what it could be.

Thank you,

Blanka

Hi Blanka

From your file there are actually 3 cells that has blank spaces in them: cells E34, B48, M48

Because of the merged cells, the macro gives 32 cells cleaned (1st time) and then 29 cells cleaned (subsequently)

The 3 cells (E34, B48, M48) are actually cleaned the 1st time while nothing got cleaned after that.

The comparison If Trim(c.Value) = "" is giving the wrong results for merged cells.

The 29 cells came from the merged cells. Somehow the SpecialCells keep on selecting them.

If you unmerged the cells, both ClearContents and vbNullString gives the correct result of 3 cells cleaned and later 0 cells cleaned if you ran it again.

I can't explain this behavior but to be on the safe side I suggest you use the codes that I suggested since it works.

My advice is to avoid merged cells as it causes lot of problem. In this case I believe it is unavoidable as the file is downloaded from somewhere else.

Sunny

You can also fix it by not clearing cells that are actually empty (which is after all fairly pointless) by making a small change to the condition:

If Trim(c.Value) = "" And Not IsEmpty(c.Value) Then

Sunny, Velouria, thank you both for your help.

Very kind of you to provide the explanations. You've been very helpful.

By the way, the files are from outside entities and we have no say in how they are generated so I'll have to live with the merged cells.

Thank you,

Blanka

Hi Blanka

You are welcome.

Let us know if you have anymore issues.

Sunny