excel command to clear contents except 2 formulas

I have an excel database that when being accessed each time l need to clear
the contents of the previous record. I have created a macro through VBA to
clear my drop down and option boxes. Does anyone know of the command that
will clear my text fields of information. The gotcha here is l have a
formula in cells c7 and c22 that l don't want whipped out. One is an age
calculator formula and the other one is a hours/minute formula calculation.
I know l can type the range with clear contents in the command. I am unsure
how you eliminate c7, c22 from that command.

Any help would be greatly appreciated

Lynda S

Can't you clear several ranges that surround your static cells as an alternative?

Lynda

Something like

Sub ClearAllButFormulae()
Range("A1:H23").SpecialCells(xlCellTypeConstants).Clear
End Sub

Will remove constants from A1:H23

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"Lynda S" <LyndaS@discussions.microsoft.com> wrote in message
news:1E39D951-4757-4F36-9B77-1183714BA1F0@microsoft.com...
>I have an excel database that when being accessed each time l need to clear
> the contents of the previous record. I have created a macro through VBA to
> clear my drop down and option boxes. Does anyone know of the command that
> will clear my text fields of information. The gotcha here is l have a
> formula in cells c7 and c22 that l don't want whipped out. One is an age
> calculator formula and the other one is a hours/minute formula
> calculation.
> I know l can type the range with clear contents in the command. I am
> unsure
> how you eliminate c7, c22 from that command.
>
> Any help would be greatly appreciated
>
> Lynda S

Lynda

Sub clearout()
Set rng1 = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
rng1.ClearContents
End Sub


Gord Dibben MS Excel MVP

On Tue, 11 Jul 2006 12:55:02 -0700, Lynda S <LyndaS@discussions.microsoft.com>
wrote:

>I have an excel database that when being accessed each time l need to clear
>the contents of the previous record. I have created a macro through VBA to
>clear my drop down and option boxes. Does anyone know of the command that
>will clear my text fields of information. The gotcha here is l have a
>formula in cells c7 and c22 that l don't want whipped out. One is an age
>calculator formula and the other one is a hours/minute formula calculation.
>I know l can type the range with clear contents in the command. I am unsure
>how you eliminate c7, c22 from that command.
>
>Any help would be greatly appreciated
>
>Lynda S

Thank you for your reply. I didn't think about that scenario. I just need
to think how l will type in the groupings

Lynda

"mrice" wrote:

>
> Can't you clear several ranges that surround your static cells as an
> alternative?
>
>
> --
> mrice
>
> Research Scientist with many years of spreadsheet development experience
> ------------------------------------------------------------------------
> mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
> View this thread: http://www.excelforum.com/showthread...hreadid=560440
>
>

Thank you for your response. I will play with this and see.

Lynda

"Nick Hodge" wrote:

> Lynda
>
> Something like
>
> Sub ClearAllButFormulae()
> Range("A1:H23").SpecialCells(xlCellTypeConstants).Clear
> End Sub
>
> Will remove constants from A1:H23
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> www.nickhodge.co.uk
> nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
>
>
> "Lynda S" <LyndaS@discussions.microsoft.com> wrote in message
> news:1E39D951-4757-4F36-9B77-1183714BA1F0@microsoft.com...
> >I have an excel database that when being accessed each time l need to clear
> > the contents of the previous record. I have created a macro through VBA to
> > clear my drop down and option boxes. Does anyone know of the command that
> > will clear my text fields of information. The gotcha here is l have a
> > formula in cells c7 and c22 that l don't want whipped out. One is an age
> > calculator formula and the other one is a hours/minute formula
> > calculation.
> > I know l can type the range with clear contents in the command. I am
> > unsure
> > how you eliminate c7, c22 from that command.
> >
> > Any help would be greatly appreciated
> >
> > Lynda S
>
>
>

Thank you for your reply. I will play with this and see

Lynda

"Gord Dibben" wrote:

> Lynda
>
> Sub clearout()
> Set rng1 = Selection.SpecialCells(xlCellTypeConstants, _
> xlTextValues)
> rng1.ClearContents
> End Sub
>
>
> Gord Dibben MS Excel MVP
>
> On Tue, 11 Jul 2006 12:55:02 -0700, Lynda S <LyndaS@discussions.microsoft.com>
> wrote:
>
> >I have an excel database that when being accessed each time l need to clear
> >the contents of the previous record. I have created a macro through VBA to
> >clear my drop down and option boxes. Does anyone know of the command that
> >will clear my text fields of information. The gotcha here is l have a
> >formula in cells c7 and c22 that l don't want whipped out. One is an age
> >calculator formula and the other one is a hours/minute formula calculation.
> >I know l can type the range with clear contents in the command. I am unsure
> >how you eliminate c7, c22 from that command.
> >
> >Any help would be greatly appreciated
> >
> >Lynda S
>
>