No Cells Were Found

Copper Contributor

When trying to clear data out of cells in Excel that are using formulas, I select the cells and and use the ‘Go to special’, ‘constants’  option. I receive the following error/message: “No Cells Were Found”

9 Replies
Of course, a cell which contains a constant is by definition one which does not contain a formula.

Regards
I guess the question then becomes, “how do I clear cells in a spreadsheet without clearing the formulas”… so I can reuse the spreadsheet? What I described was the solution I found when searching the internet.

@BrookC84 

 

Bit confused. You appear to be contradicting yourself. First you said that you are "trying to clear data out of cells in Excel that are using formulas". What exactly does that mean? Now you seem to be suggesting that you want to clear all cells which don't contain formulas, for which the Go To Constants option would indeed be ideal.

 

Regards

@JosWoolley 

I’m trying to create a clean/empty spreadsheet (except for names, column labels, etc)… but maintain all the formulas that are in certain cells.

Maybe you're not using the Go To command properly. You should make sure that you don't have more than a single cell selected in the worksheet prior to using it, otherwise it will operate over the selected range only. For example, if you have highlighted column A, then the Go To command will only look in column A, not the rest of the worksheet.

@JosWoolley 

Maybe I’m not explaining myself clearly.  I have a spreadsheet I created years ago which does exactly what I want it to do.  I simply want to clear the entire spreadsheet except for its title and column names/labels… but leave all the hidden formulas I created in certain cells.  How do I accomplish this simple task :smiling_face_with_smiling_eyes:?

In that case your original plan of using the Go To command with Constants should work just fine, provided of course that prior to running that command you ensure that the selected range within the worksheet does not include any of the constants that you wish to keep (title, column names, etc.).

Regards
Yea… that’s what I thought. Example: I have several rows in a column that have a variation of the equation “=SUM(H6-B6)” that all currently have values in the cells. I highlighted just those cells and went to the ‘Go to special’, ‘constants’ option… and hoping to simply click on delete… but instead received the “No Cells Were Found” message. Very strange.

@BrookC84 

 

Not strange at all. As I said in my very first reply, a cell which contains a formula cannot be considered a constant. A constant is an entry which is not the result of a formula, for example one which has been manually entered into a cell.

 

Regards