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

excel command to clear contents except 2 formulas
 

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

excel command to clear contents except 2 formulas
 

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
HIS


"Lynda S" wrote in message
...
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

excel command to clear contents except 2 formulas
 

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
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

excel command to clear contents except 2 formulas
 

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

excel command to clear contents except 2 formulas
 

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
HIS


"Lynda S" wrote in message
...
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

excel command to clear contents except 2 formulas
 

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
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