vba to clear cells but leave specific value

[dami916]

Hello,
Does anyone knows how to set VBA to clear a range but leave a specific value?

I was using this:

Sub ClearSheet()
' ClearSheet Macro


Range("B6:H25").Select
On Error GoTo Error_handler:

Selection.SpecialCells(xlCellTypeConstants, 1).Select
Selection.ClearContents

Error_handler:
MsgBox "Cleared"

End Sub


but it only clears numbers. I need it to clear everything with exception of a specific value.

thank you

 

[Fluff]

By everything do you mean all cells in the range to be cleared, with the exception of the specific value?
Also what is the specific value?

 

[dami916]

By everything do you mean all cells in the range to be cleared, with the exception of the specific value?
Also what is the specific value?

Yes, clear everything with exception of the value "OFF"

This VBA does the job but it's very slow:

Sub DoNotContainClearCells()
'Clear out all cells that do not contain a specific word/phrase
Dim rng As Range
Dim cell As Range
Dim ContainWord As String
'What range do you want to search?
Set rng = Range("b6:h25")
'What phrase do you want to test for?
ContainWord = "OFF"
'Loop through each cell in range and test cell contents
For Each cell In rng.Cells
If cell.Find(ContainWord) Is Nothing Then cell.ClearContents
Next cell
End Sub

 

[Joe4]

See if this speeds things up for you:

Sub DoNotContainClearCells()
'Clear out all cells that do not contain a specific word/phrase
Dim rng As Range
Dim cell As Range
Dim ContainWord As String

[COLOR=#ff0000]Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual[/COLOR]

'What range do you want to search?
Set rng = Range("b6:h25")
'What phrase do you want to test for?
ContainWord = "OFF"
'Loop through each cell in range and test cell contents
For Each cell In rng.Cells
    If cell.Find(ContainWord) Is Nothing Then cell.ClearContents
Next cell
[COLOR=#ff0000]
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True[/COLOR]

End Sub

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub DoNotContainClearCells()
  Cells.Replace "OFF", "#N/A", xlWhole, , , , False, False
  Cells.SpecialCells(xlConstants, xlNumbers + xlTextValues).ClearContents
  Cells.SpecialCells(xlConstants).Value = "OFF"
End Sub[/td]
[/tr]
[/table]

 

[dami916]

See if this speeds things up for you:

Sub DoNotContainClearCells()
'Clear out all cells that do not contain a specific word/phrase
Dim rng As Range
Dim cell As Range
Dim ContainWord As String

[COLOR=#ff0000]Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual[/COLOR]

'What range do you want to search?
Set rng = Range("b6:h25")
'What phrase do you want to test for?
ContainWord = "OFF"
'Loop through each cell in range and test cell contents
For Each cell In rng.Cells
    If cell.Find(ContainWord) Is Nothing Then cell.ClearContents
Next cell
[COLOR=#ff0000]
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True[/COLOR]

End Sub

this works perfect, thanks a lot

 

[Rick Rothstein]

this works perfect, thanks a lot

Just wondering if you saw the code I posted in Message #5 or not?

 

[Fluff]

beaten 2it

 

[dami916]

Give this macro a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub DoNotContainClearCells()
  Cells.Replace "OFF", "#N/A", xlWhole, , , , False, False
  Cells.SpecialCells(xlConstants, xlNumbers + xlTextValues).ClearContents
  Cells.SpecialCells(xlConstants).Value = "OFF"
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]

this works great too but clears the whole worksheet I only need b6:h25
Thank you for your reply

 

[Rick Rothstein]

this works great too but clears the whole worksheet I only need b6:h25

Sorry, I forgot about that. Give this a try then...

[table="width: 500"]
[tr]
	[td]Sub DoNotContainClearCells()
  With Range("B5:H25")
    .Replace "OFF", "#N/A", xlWhole, , , , False, False
    .SpecialCells(xlConstants, xlNumbers + xlTextValues).ClearContents
    .SpecialCells(xlConstants).Value = "OFF"
  End With
End Sub[/td]
[/tr]
[/table]