Log In
Register
Home
October 17, 2015
Offline
I use the following macro to clear empty cells all the time and I love it.
Recently I was using this macro on a file which had some merged cells. The macro threw the following error on the c.ClearContents line: "Run-time error '1004': We can't do that to a merged cell."
What needs to be altered in this macro so it also works for files with merged cells?
Thank you.
Blanka
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
c.ClearContents
iCnt = iCnt + 1
End If
Next c
sTemp = "Cleared " & iCnt & " cell"
If iCnt <> 1 Then sTemp = sTemp & "s"
MsgBox sTemp
End Sub
See Answer
VIP
Trusted Members
June 25, 2016
Offline
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
Answers Post
Trusted Members
Moderators
November 1, 2018
Offline
You could use:
c.value = vbnullstring
which will work with merged cells too.
October 17, 2015
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
I can't reproduce that. I get a number the first time and then 0 thereafter. What exact code did you use?
October 17, 2015
Offline
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.
VIP
Trusted Members
June 25, 2016
Offline
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
October 17, 2015
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
Trusted Members
Moderators
November 1, 2018
Offline
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
October 17, 2015
Offline
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
1 Guest(s)
