Clearing Named Ranges Clears Entire Sheet

[krakken]

I have a series of named ranges on a sheet. I want to clear the contents and not the formulas. When I run the below, it clears the named ranges and then clears the entire sheet. Also, if I have no constants, it gives me an error. Help! TIA.

Private Sub CommandButton1_Click()


On Error Resume Next


Range("Inputs_1").Select
Selection.SpecialCells(xlCellTypeConstants).Select
Selection.ClearContents
Range("Inputs_2").Select
Selection.SpecialCells(xlCellTypeConstants).Select
Selection.ClearContents
Range("Inputs_3").Select
Selection.SpecialCells(xlCellTypeConstants).Select
Selection.ClearContents

End Sub

 

[AlphaFrog]

Try this...

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click()


[COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]


Range("Inputs_1").SpecialCells(xlCellTypeConstants).ClearContents
Range("Inputs_2").SpecialCells(xlCellTypeConstants).ClearContents
Range("Inputs_3").SpecialCells(xlCellTypeConstants).ClearContents


[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

If the named ranges are on a different sheet than the command button, qualify the ranges with the sheet e.g.;

[B]Sheets("Sheet1").[/B]Range("Inputs_1").SpecialCells(xlCellTypeConstants).ClearContents

 

[krakken]

When I try that it clears all cells on the excel sheet. Even data outside of the range.

Try this...

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click()


[COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]


Range("Inputs_1").SpecialCells(xlCellTypeConstants).ClearContents
Range("Inputs_2").SpecialCells(xlCellTypeConstants).ClearContents
Range("Inputs_3").SpecialCells(xlCellTypeConstants).ClearContents


[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

If the named ranges are on a different sheet than the command button, qualify the ranges with the sheet e.g.;

[B]Sheets("Sheet1").[/B]Range("Inputs_1").SpecialCells(xlCellTypeConstants).ClearContents

 

[AlphaFrog]

I suspect something is off with one of your named ranges. Thie code worked in my test.

 

[krakken]

Yup! That was it. I double checked after your suggestion and one was off. THANKS!

I suspect something is off with one of your named ranges. Thie code worked in my test.