How to clear values not formulas from a range

[Mervyn Thomas]

Does anyone know how to clear (delete) all the data in a named range without
touching the formulas which are spread around the range in an unhelpful
pattern. I can't figure out the VB syntax to write a macro for this.

--
Regards
Mervyn

[Leo Heuser]

Mervyn

If I have understood your question correctly, this
sub will do the job. The named range is "DataBlock"

Sub RemoveData()
'leo.h...@adslhome.dk, Dec. 11., 2001
Dim CheckRange As Range
Dim Cell As Range

Set CheckRange = Range("DataBlock")

For Each Cell In CheckRange
If Cell.HasFormula = False Then
Cell.ClearContents
End If
Next Cell
End Sub

--
Best regards
Leo Heuser
MVP Excel

"Mervyn Thomas" <mervyn...@ntlworld.com> skrev i en meddelelse
news:SilR7.349$bu4.1...@news11-gui.server.ntli.net...

[Mervyn Thomas]

Many thanks - that code is almost there. I didn't mention I only want to
clear cells with numbers in them and leave labels and formula. I do find it
very difficult to find the right syntax in VBA help and I failed to find
your suggestion "cell.HasFormula" . I now want a "Cell.HasLabel" or
Cell.HasNumericEntry" or something like that. How exactly do you find these
properties?
--
Regards
Mervyn

"Leo Heuser" <leo.h...@get2net.dk> wrote in message
news:#YIbdLjgBHA.2012@tkmsftngp03...

[Leo Heuser]

In the expression "cell.HasFormula", "cell" is the name of a variable
used, when the FOR - EACH loop checks each cell in the named range.
I could have used another name e.g. "CellToCheck" (done below).
The property is "HasFormula", and that's the word, you can find in VBA help.

For a routine to remove only numbers, try the altered routine below.
If you have a number as a label e.g. 456, enter it
as =456, and it will not be deleted (since it now is a formula).

Sub RemoveData()
'leo.h...@adslhome.dk, Dec. 11., 2001
Dim CheckRange As Range

Dim CellToCheck As Range

Set CheckRange = Range("DataBlock")

For Each CellToCheck In CheckRange
If CellToCheck.HasFormula = False _
And IsNumeric(CellToCheck.Value) Then
CellToCheck.ClearContents
End If
Next CellToCheck
End Sub

--
Best regards
Leo Heuser
MVP Excel

"Mervyn Thomas" <mervyn...@ntlworld.com> skrev i en meddelelse

news:KwmR7.1953$6r6.2...@news2-win.server.ntlworld.com...

[David McRitchie]

Hi Mervyn, (correction, posted with email copy)

Should have included "On Error" in case there is nothing
to be removed in your selection. (not found condition)

The Intersect is included to overcome the Excel habit
of extending a single cell selection.

Sub ClearNumberConstants()
On Error Resume Next
Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlNumbers)).Clear
End Sub
Sub ClearConstants()
On Error Resume Next
Intersect(Selection, _
Selection.SpecialCells(xlConstants)).Clear
End Sub

Change Selection. to your Range("NamedRange") and
generalized subroutine names to match your actual question.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "Mervyn Thomas" <mervyn...@ntlworld.com> wrote...

[David McRitchie]

Hi Mervyn,

The Intersect is included to overcome the Excel habit
of extending a single cell selection.

Change subroutine names and use Range("DataBlock")
instead of selection to match your specific question
if that is your real intent.

Sub ClearNumberConstants()

Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlNumbers)).Clear
End Sub
Sub ClearConstants()

Intersect(Selection, _
Selection.SpecialCells(xlConstants)).Clear
End Sub

Also see an example of clearing constants in macro InsrtRow
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Mervyn Thomas" <mervyn...@ntlworld.com> wrote in message news:KwmR7.1953$6r6.232021@news2-

> Many thanks - that code is almost there. I didn't mention I only want to
> clear cells with numbers in them and leave labels and formula. I do find it
> very difficult to find the right syntax in VBA help and I failed to find
> your suggestion "cell.HasFormula" . I now want a "Cell.HasLabel" or
> Cell.HasNumericEntry" or something like that. How exactly do you find these
> properties?
>

> "Leo Heuser" <leo.h...@get2net.dk> wrote in message
> news:#YIbdLjgBHA.2012@tkmsftngp03...
> > Mervyn
> >
> > If I have understood your question correctly, this
> > sub will do the job. The named range is "DataBlock"
> >
> > Sub RemoveData()
> > 'leo.h...@adslhome.dk, Dec. 11., 2001
> > Dim CheckRange As Range
> > Dim Cell As Range
> > Set CheckRange = Range("DataBlock")
> > For Each Cell In CheckRange
> > If Cell.HasFormula = False Then
> > Cell.ClearContents
> > End If
> > Next Cell
> > End Sub
> >

[Leo Heuser]

I *knew* there was a faster way, David <bg>

"David McRitchie" <dmcri...@msn.com> skrev i en meddelelse
news:eV2EigkgBHA.2384@tkmsftngp03...

[Mervyn Thomas]

Many thanks Leo's solution works and I am sure David's does as well but I
haven't tried it

--
Regards
Mervyn

"Leo Heuser" <leo.h...@get2net.dk> wrote in message

news:u1SKDIkgBHA.2392@tkmsftngp02...

[Paul]

Try the toolkit at http://ukww.net/patools which will do this (and a whole
lot more besides!).

HTH
Paul

"Mervyn Thomas" <mervyn...@ntlworld.com> wrote in message

news:SilR7.349$bu4.1...@news11-gui.server.ntli.net...

[David McRitchie]

There is a lot to be said about using SpecialCells vs checking
each cell. This would be most noticeable if you selected
entire columns or entire rows.

But try this for a range in Leo's solution it will take several
minutes enough to fill and eat a small plate of food at least
for two helpings. Range(B2:M4000)
I was going to cancel it after brushing my teeth but it did
finish. Of course you could turn off calculation and
screen updating, which would probably help a lot. Iexpect
that Leo would have turned them off in a real application.

Then try same range for my solution, don't blink, okay it
took perhaps 2 seconds.

My CPU is a Pentium III 600MG 128Meg, my old computer
though not working is a Pentium II 200MHz 64Meg.
Wasting cycles may be a reason to buy a bigger computer
but it doesn't help get the work done.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Mervyn Thomas" <mervyn...@ntlworld.com> wrote ...

> Many thanks Leo's solution works and I am sure David's does as well but I
> haven't tried it
>

[David McRitchie]

Please use your own name and email address, I got a message
from "Mervyn Thomas" <mervyn...@ntlworld.com>
saying that the email address was his but that it was not
his question. I have seen where people think they are making
up a name but since a real domain exists errors in userid go
to the webmaster (owner) of the site. This is the first I've seen
someone actually purposely using someone else's name.

>>This e-mail was sent to me by mistake. Although the address is correct I did
>>not send the query.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Mervyn Thomas" <mervyn...@ntlworld.com> wrote in message news:KYqR7.445$TI5....@news11-gui.server.ntli.net...