Clear value in cells without formulas

[Bengt]

I am trying to clear the values of cells in a certain range that don't have a formula. I tried the code below, and while it runs through the two loops without problems, it does not do what I want.

Am I wrong in assuming that the "HasFormula=False" should work? What is the proper way of asking Excel whether or not a certain cell contains a formula?

Has anyone got an answer for me?

Thanks

Bengt

(RangeName is defined as a string that contains the name of the area)
For Row = 1 To Maxrow
Application.StatusBar = String(Row, "*")
For Column = 1 To Maxcol
If Range(RangeName).Cells(Row, Column).HasFormula = False Then
Range(RangeName).Cells(Row, Column).Value = ""
End If
Next Column
Next Row

 

[Andrew Poulsom]

HasFormula should work.

 

[Jonmo1]

Try something like

I would suggest not using Row and Column as variables, they may be reserved words...Try MyRow and MyColumn

Range("A1").Resize(MaxRow, MaxCol).SpecialCells(xlCellTypeConstants, 23).ClearContents

 

[Bengt]

Thank you,
As far as Row/Column goes, it seems like Excel doesn't like if you call them Rows/Columns. I had that first and it didn't work, but when I removed the ending "s", it did work.

As for your solution, I don't really understand how to loop through every cell in the area if I write it like that. (And BTW, my Maxrow/Maxcol variable values came from Range(Rangename).Rows.Count/Range(Rangename).Columns.Count).

Anyway, in the code that you suggest, how do you single out cells that have a formula from those who don't? Why didn't my original code work?

Bengt

 

[Jonmo1]

The method I suggested is NOT a loop, it does ALL the non formula cells in one stroke.

It's the SpecialCells method...
To get an idea how it works, highlight your data and click Edit - Goto - Special - Constants - OK


Hope that helps.

 

[Bengt]

Actually, your answer was very helpful. I found out when I got home from work and had some time to experiment with it. It seems that that is the best way to do it, so thank you very much. I still wonder why my original attempt failed though.

Bengt

 

[Bengt]

One more question however: What excactly does the Resize method of the Range-object do?