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.

 

[keef2]

just change cell.delete to cell.ClearContents

 

[keef2]

Sub deleteSingles()
Dim cell As Range

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

End Sub

 

[HaHoBe]

Hi Zembu,

instead of referring to all constants in the sheet maybe use a constant for the address of the range like

Sub ClearContentsSingles()
Dim rngCell As Range

Const cstrRange As String = "A2:E50"

For Each rngCell In Range(cstrRange)
  If Len(rngCell.Value) = 1 Then rngCell.ClearContents
Next rngCell

End Sub

Ciao,
Holger

 

[Fluff]

@HaHoBe that will also clear cells with formulae that return a single character.

 

[Zembu]

Sub deleteSingles()
Dim cell As Range

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

End Sub

Thanks Keef - I needed to define the range as well HaHoBe seems to have it as I need.

 

[Zembu]

Hi Zembu,

instead of referring to all constants in the sheet maybe use a constant for the address of the range like

Sub ClearContentsSingles()
Dim rngCell As Range

Const cstrRange As String = "A2:E50"

For Each rngCell In Range(cstrRange)
  If Len(rngCell.Value) = 1 Then rngCell.ClearContents
Next rngCell

End Sub

Ciao,
Holger

Thanks HaHoBe - I am running yours at the moment - it is taking a long time - my selection of cells is 1,365,910. I could see that it cleared the contents of some so I guess it will get there eventually. Many thanks

 

[HaHoBe]

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

 

[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

Many thanks HaHoBe. My range does not include formulas so that's OK. I will let it run for a while more, but will also copy your latest and try that another time.
Cheers,

Andrew

 

[Zembu]

@HaHoBe that will also clear cells with formulae that return a single character.

Thanks Fluff!! No formulas in my range so that's OK.