In Excel 2010 VBA, how do I select all and only the cells that have NO formula?

In order to update an Excel tax application for use in a new tax period, I want to start from the completed worksheet(s) of the previous period and delete some/all the already-entered data but leave the formulas intact. I know how to do this in VBA by looping through all the used cells in a worksheet and removing the contents of each one that has a formula.

But this is relatively slow! I could do it faster by using the Excel command to select all cells WITH a formula if there were a quick method of inverting that selection or selecting all the used range that does NOT intersect with that selection. Is there?
Brian Hershman(retired)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
This will select all cells with formulas.

ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
1
Saurabh Singh TeotiaCommented:
Basically what i understood you want to select only values...not formulas..Then a small variation from what martin said it will be...

ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)

Open in new window


Saurabh...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
Do you need empty as well as literal/constant valued cells?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Rgonzo1971Commented:
Hi,

you could use

Sub Macro1()
Set rngRange = RangeComp(ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas), ActiveSheet.UsedRange)
End Sub
Function RangeComp(rngA As Range, rngB As Range) As Range
    ' Returns the Relative Complement of rngA in rngB
    ' RangeComp = rngB - rngA
 
    Dim cell    As Range
 
    If rngA Is Nothing Then
        Set RangeComp = rngB
 
    ElseIf Not rngB Is Nothing Then
        If Not rngA.Worksheet Is rngB.Worksheet Then Exit Function
 
        For Each cell In rngB
            If Intersect(cell, rngA) Is Nothing Then
                If RangeComp Is Nothing Then Set RangeComp = cell
                Set RangeComp = Union(RangeComp, cell)
            End If
        Next
    End If
End Function

Open in new window

Regards
1
Martin LissOlder than dirtCommented:
Was just about to post mine so I will anyway. It selects all non-formula cells.

Dim rngF As Range
Dim rngNoFormula As Range
Dim cel As Range

Set rngF = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)

For Each cel In ActiveSheet.UsedRange.Cells
    If Intersect(cel, rngF) Is Nothing Then
        If rngNoFormula Is Nothing Then
            Set rngNoFormula = cel
        Else
            Set rngNoFormula = Application.Union(rngNoFormula, cel)
        End If
    End If
    rngNoFormula.Select
Next

Open in new window

0
Brian Hershman(retired)Author Commented:
Martin's suggestion was relevant but missed the fact that I had already got to there. Therefore it was not THE solution. But Saurabh's IS!
My thanks to both of them.
0
Brian Hershman(retired)Author Commented:
Thanks to all the experts. Saurabh's is still the only complete solution to the question as asked, but the others provide some interesting insights.
0
Saurabh Singh TeotiaCommented:
Brian.. Always Happy to help .. :-)

Saurabh...
0
Roy CoxGroup Finance ManagerCommented:
When using SpecialCells it is always a good idea to use an error handler to avoid the error raised if no cells match the criteria, e.g

On Error Resume Next
ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

Open in new window

0
Brian Hershman(retired)Author Commented:
I am grateful for all the information the Experts have given me. Not one was the full answer to my problem. But they all directed me to features of Excel/VBA that I was not sufficiently aware of and so helped me to devise an efficient solution not only to this problem but also to several other actual or potential problems.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.