Delete Cell value if it contains any text in Excel

[oldb]

Hi,

I need to delete cell values if they contain any text on multiple spreadsheets (but not all, every sheet after the 5th one).
Range would be E3 to GW16 on every sheet.

Is there an easy way to do this?
Thanks for your help

 

[diddi]

hi and welcome toe MrExcel
you could have a command button that you click with a short macro behind it

 

[oldb]

hi and welcome toe MrExcel
you could have a command button that you click with a short macro behind it

Thanks,

any idea what I should put in the macro? really no expert here

 

[diddi]

how are your sheets named. is there an easy way to pick either the ones you do not want to clear or the other way around. once i know the sheets, the macro is only a few lines and you dont really need to know how it works. you can have a button on a sheet that you click and the macro will do its work

 

[Peter_SSs]

I need to delete cell values if they contain any text on multiple spreadsheets (but not all, every sheet after the 5th one).
Range would be E3 to GW16 on every sheet.

Try this with a copy of your workbook.

Sub Clear_Cell_Values()
  Dim i As Long
  
  For i = 6 To Sheets.Count
    Sheets(i).Range("E3:GW16").ClearContents
  Next i
End Sub

 

[oldb]

Try this with a copy of your workbook.

Sub Clear_Cell_Values()
  Dim i As Long
 
  For i = 6 To Sheets.Count
    Sheets(i).Range("E3:GW16").ClearContents
  Next i
End Sub

Hi peter,

this already helped a lot, but it just clears everything. I need it to clear only if there is text and leave it if there are numbers.

Thanks

 

[oldb]

how are your sheets named. is there an easy way to pick either the ones you do not want to clear or the other way around. once i know the sheets, the macro is only a few lines and you dont really need to know how it works. you can have a button on a sheet that you click and the macro will do its work

Hi diddi,

well there are quite a lot of sheets so I think its easier to tell you the ones to be left out. (They're in japanese btw. and the star is included, don't know if that has any effect)

★登録車種情報
★管理帳票
★管理帳票 (2)
★原紙
★原紙_2枚目

These are the first 5 sheets and I need to clear cells in the range I mentioned in all the sheets that follow, but only if they contain any text not if they have numbers.
Thanks a lot

 

[Peter_SSs]

Not sure how it will go with the Japanese characters but try this

Sub Clear_Text_Values()
  Dim i As Long
  
  On Error Resume Next
  For i = 6 To Sheets.Count
    Sheets(i).Range("E3:GW16").SpecialCells(xlCellTypeConstants, xlTextValues).ClearContents
  Next i
  On Error GoTo 0
End Sub

 

[jolivanes]

Re: "if they contain any text"
What is considered any text?

1234abcd567
or
abcdef
or
ghij klmn 987
or
1234 formatted as text

 

[oldb]

Not sure how it will go with the Japanese characters but try this

Sub Clear_Text_Values()
  Dim i As Long
 
  On Error Resume Next
  For i = 6 To Sheets.Count
    Sheets(i).Range("E3:GW16").SpecialCells(xlCellTypeConstants, xlTextValues).ClearContents
  Next i
  On Error GoTo 0
End Sub

Thanks Peter this seems to work perfectly