Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert rows without copying borders and shading
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert rows without copying borders and shading
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert rows without copying borders and shading
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert rows without copying borders and shading
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert rows without copying borders and shading
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert rows without copying borders and shading
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
freeze borders and shading | Excel Discussion (Misc queries) | |||
Copying cell border formatting, without shading. | Excel Programming | |||
insert row keeping borders | Excel Discussion (Misc queries) | |||
Copyin text only...not borders or shading | Excel Worksheet Functions | |||
Same formula in each cell of column; insert rows w/o copying down | Excel Discussion (Misc queries) |