VBA Clear contents column

[pantakos]

Hello all,

I am trying to clear only the numeric values from column E.

With my little knowledge (and help from google) I have created this

Sub ClearContentsITEMS()
ActiveSheet.Range("E1", Range("E" & Columns("E").SpecialCells(xlCellTypeConstants, xlNumbers).Row)).ClearContents
MsgBox "ITEMS form cleared!"
End Sub

And error 1004 ...
What I am doing wrong?

I want to clear all the contents from column E that are Numbers (keep formulas, special char, text etc)

Any help will be appreciated.

Thank you !

 

[DanteAmor]

Try this:

Sub ClearContentsITEMS()
  Range("E1", Range("E" & Rows.Count).End(3)).SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
  MsgBox "ITEMS form cleared!"
End Sub

 

[pantakos]

@DanteAmor ! thank you for your quick reply, working like a charm.
The problem now is that if there is notthing to clear VBA gives me an error. If I enter data at column E everything is fine
Strange...
Is there a way to check if there is something to clear, then clear, if not thent display a message like "Nothing to Clear!"
And something more, is it possible to include and another column from the same sheet? Lets say Column K

 

[DanteAmor]

Is there a way to check if there is something to clear, then clear, if not thent display a message like "Nothing to Clear!"
And something more, is it possible to include and another column from the same sheet? Lets say Column K

Try:

Sub ClearContentsITEMS()
  Dim n As Variant
  On Error Resume Next
  Range("E1", Range("E" & Rows.Count).End(3)).SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
  n = Err.Number
  If n = 0 Then
    MsgBox "Column E: ITEMS form cleared!"
  Else
    MsgBox "Column E: Nothing to Clear!"
  End If
  On Error GoTo 0
  
  On Error Resume Next
  Range("K1", Range("K" & Rows.Count).End(3)).SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
  n = Err.Number
  If n = 0 Then
    MsgBox "Column K: ITEMS form cleared!"
  Else
    MsgBox "Column K: Nothing to Clear!"
  End If
  On Error GoTo 0
  
End Sub

 

[pantakos]

You are the boss! Works Great! Thank you so much!

 

[pantakos]

@DanteAmor And one last !
Is it possible to combine both to only have one message (one for cleared and one for not cleared).
Combine mean E & K at the same formula

 

[DanteAmor]

Is it possible to combine both to only have one message

Try:

Sub ClearContentsITEMS()
  Dim msg As String
  
  On Error Resume Next
  Range("E1", Range("E" & Rows.Count).End(3)).SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
  msg = "Column E: " & IIf(Err.Number = 0, "ITEMS form cleared!", "Nothing to Clear!")
  On Error GoTo 0
  
  On Error Resume Next
  Range("K1", Range("K" & Rows.Count).End(3)).SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
  msg = msg & vbCr & "Column K: " & IIf(Err.Number = 0, "ITEMS form cleared!", "Nothing to Clear!")
  On Error GoTo 0
  
  MsgBox msg
End Sub

 

[pantakos]

My wrong!
Saying combine mean that display one message if both columns are cleared or not, no matter if one of them has data
Hope you understand, either way the above script do the job just fine!