Clear contents of row but preserve formulas?

  • Thread starter Thread starter debartsa
  • Start date Start date
D

debartsa

Hi Everybody,

In Excel 97 I'm using vba and the Object Model to copy a row and insert its
copy elsewhere. My problem is that with the newly inserted row I'd like to
clear the entire rows values that were copied over, but preserve the
formulas the row contains. I have only found the Range.Clear method which
doesn't preserve the formulas?

Does anybody know what syntax I would use instead?

Thanks for any help!
Sam
 
Hi Sam,

Use the macro recorder with Edit>Goto, Special, Constants, Edit>Clear,
Contents

That will give you the basis for the code, which of course can be simplified

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Thanks for the reply Niek!

Unfortunately Excel 97 doesn't structure it's Edit menu that way... I'll try
to find if that feature even exists in '97

Thanks
Sam
 
Tom Ogilvy wrote
activeCell.EntireRow.Specialcells(xlConstants).Clear
Click to expand...

I tried to modify this for the following loop:

cols = ActiveSheet.UsedRange.Columns.Count
Set rng = Range(Cells(5, 1), Cells(Rows.Count, 1).End(xlUp).Offset(-6, 0))
For Each c In rng
If c = Old_Member Then
c.EntireRow.SpecialCells(xlConstants).Clear
End If
Next c
rng.Resize(, cols).Sort Key1:=Range("A1"), Order1:=xlAscending

Problem is this resulted in some cells in the row immediately below c lost
their borders :(

Any way to preserve them?
 
David Turner wrote
Problem is this resulted in some cells in the row immediately below c
lost their borders :(
Click to expand...

Nevermind. Substituting .ClearContents for .Clear seems to have worked.
 
Back
Top