Good morning, Don,

Terrified though I am of correcting ANYONE on here, yes, I did try it -
which is how I found it didn't work, because it just inserted one cell down!
:-)

Regards

Pete


"Don Guillett" wrote:

I apologize. Went back and did this.

Sub insertrownoformat()
With ActiveCell
.EntireRow.Insert
.Offset(-1).EntireRow.ClearFormats
End With
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...

Pete, Did you TRY IT FIRST???
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Pete Rooney" wrote in message
...
Doesn't this only incert a cell down as against a whole row, which would
be:

Sub InsertRowNoFormat()
With ActiveCell
.EntireRow.Insert
.EntireRow.ClearFormats
End With
End Sub

Cheers

Pete

"Don Guillett" wrote:

Sub insertrownoformat()
With ActiveCell
.Rows.Insert
.ClearFormats
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"XP" wrote in message
...
I am coding a sheet in which I need to insert multiple rows and I have
a
function that I can call that does this splendidly. The problem arises
if
you
have a row with shading and borders ABOVE the row on which you are
doing
an
insert, then the borders and shading from the row above is copied down
when
the insert is done.

I just want to do a row insert without the cell shading and borders
being
copied down using VBA code. Can someone give me an easier way than
having
to
reformat everything every time?

My current function I'm using is below (remove row wrapping):

Public Function RowsInsert(argStartCell As String, argRowsToInsert As
Long)
Range(Range(argStartCell).Address,
Range(Range(argStartCell).Offset(argRowsToInsert,
0).Address)).EntireRow.Insert Shift:=xlDown
End Function