Insert rows without copying borders and shading

  • Thread starter Thread starter XP
  • Start date Start date
X

XP

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
 
I believe a function must return a value, so what you have written should
probably be a Sub since it is designed to perform an action. You would have
to use the PasteSpecial method to exclude the format properties when copying
data from one range to another. e.g. MySheet.myRange.PasteSpecial
Paste:=xlPasteValues
 
Sub insertrownoformat()
With ActiveCell
.Rows.Insert
.ClearFormats
End With
End Sub
 
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
 
I apologize. Went back and did this.

Sub insertrownoformat()
With ActiveCell
.EntireRow.Insert
.Offset(-1).EntireRow.ClearFormats
End With
End Sub
 
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
 
Back
Top