Prev: Unable to get the PivotItem Property of the Pivotfield Class
Next: .SendMail how do you turn off Alert "This program is trying to send...
From: XP on 18 Jul 2008 10:28
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

From: JLGWhiz on 18 Jul 2008 10:51
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

"XP" wrote:

> 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
>
From: Don Guillett on 18 Jul 2008 10:51
Sub insertrownoformat()
With ActiveCell
.Rows.Insert
.ClearFormats
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1(a)austin.rr.com
"XP" <XP(a)discussions.microsoft.com> wrote in message
news:18D7A882-062B-4740-95C9-4C237AC2503A(a)microsoft.com...
>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
>

From: Pete Rooney on 18 Jul 2008 11:37
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
> dguillett1(a)austin.rr.com
> "XP" <XP(a)discussions.microsoft.com> wrote in message
> news:18D7A882-062B-4740-95C9-4C237AC2503A(a)microsoft.com...
> >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
> >
>
>
From: Don Guillett on 18 Jul 2008 11:50

Pete, Did you TRY IT FIRST???
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1(a)austin.rr.com
"Pete Rooney" <PeteRooney(a)discussions.microsoft.com> wrote in message
news:3D7547F5-7477-45A2-A394-4A5711F820D8(a)microsoft.com...
> 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
>> dguillett1(a)austin.rr.com
>> "XP" <XP(a)discussions.microsoft.com> wrote in message
>> news:18D7A882-062B-4740-95C9-4C237AC2503A(a)microsoft.com...
>> >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
>> >
>>
>>

 |  Next  |  Last
Pages: 1 2
Prev: Unable to get the PivotItem Property of the Pivotfield Class
Next: .SendMail how do you turn off Alert "This program is trying to send...