In a defined range, clear contents of all cells containg only one character

[Zembu]

Tazguy37 gave a solution to a slightly different question!

He wrote;

Sub deleteSingles()
Dim cell As Range

On Error Resume Next
For Each cell In Cells.SpecialCells(xlCellTypeConstants)
If Len(cell.Value) = 1 Then cell.Delete
Next cell

End Sub


This solution is almost good for me! I want to (not delete but clear contents) of cells containg a single character, and be able to set a specific range to be operated on.

 

[Zembu]

Hi Zembu,

please mind what Fluff has put in: it will kill formulas.

If you care about the time the macro takes maybe try

Sub ClearContentsSingles_2()
Dim rngCell As Range

Const cstrRange As String = "A2:E50"

With Application
  .ScreenUpdating = False
  .EnableEvents = False
  .Calculation = xlCalculationManual
End With

On Error Resume Next
For Each rngCell In Range(cstrRange).SpecialCells(xlCellTypeConstants)
  If Len(rngCell.Value) = 1 Then rngCell.ClearContents
Next rngCell

With Application
  .ScreenUpdating = True
  .EnableEvents = True
  .Calculation = xlCalculationAutomatic
End With

End Sub

Ciao,
Holger

Hi Holger,
I've now tried both methods and your second one is best for me. For the first, Excel was "not responding" and I gave up after a few hours. I tested both methods this morning on a smaller selection of cells and both worked fine. Then I tried your second on the large selection - as I said 1,365,910 cells and it worked perfectly taking around 5 minutes to finish. PC has only 8GB memory, 64 bit operating system, Windows 7. Many thanks for helping out on this! Cheers, Andrew