excel vba insert row with formatting

Question

I have a macro which inserts a number of rows depending on user input in Excel 2007. Everything works but I still have a minor issue. I want to copy the complete formatting from the row above. It only works for some cells in the row.

Here is the code for insertion:

 Rows("B:B").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Is it possible to change it?

best, US

why don't you copy/paste and then `ClearContents`? Or you could simply Copy/Paste Format (just record a macro and adapt the code) — JMax, Nov 18 '11 at 15:04

score 5 · Accepted Answer · answered Nov 18 '11 at 15:22

I am not sure exactly how you are inserting your rows, but if you insert the row below an existing one, it will by default take on the formatting of the row above it, at least it does when you use this type of syntax:

Range("B2").EntireRow.Offset(1, 0).Insert

In this example, it will insert a row below B2 and the format (say, B2's row is highlighted yellow) will be yellow as well. It might have to do with the fact that this type of inserting specifies exactly under which row to insert.

score 1 · Answer 2 · answered Aug 03 '19 at 01:11

1

For those that don't want to inherit formatting from parent range use .cells(). .rows(2).insert will inherit formatting while .cells(2).insert will not.

answered Aug 03 '19 at 01:11

Dam Gal

135
2
11

score 0 · Answer 3 · edited Jun 13 '13 at 10:04

0

Dim j As Long, r As Range
j = InputBox("type the number of rows to be insered")

Set r = Range("A4")
Do
    Range(r.Offset(1, 0), r.Offset(j, 0)).EntireRow.Insert
    Set r = Cells(r.Row + j + 1, 1)

If r.Offset(1, 0) = "" Then Exit Do Loop

edited Jun 13 '13 at 10:04

tkanzakic

5,499
16
34
41

answered Jun 13 '13 at 09:44

nagma

1

user366121 · Answer 4 · 2011-11-18T15:53:36.800

0

The answer is the first comment.

New code:

Rows(CStr(InsRowNumber - 1) & ":" & CStr(InsRowNumber - 1)).Copy
Rows(CStr(InsRowNumber) & ":" & CStr(InsRowNumber)).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

edited Nov 18 '11 at 15:53

answered Nov 18 '11 at 15:18

user366121

3,203
9
48
69

I suggest you edit your answer with the code you've used and then accept it after the delay. Thus, the thread will be closed (i.e. answered) and it will help further users who will find this thread >if SO helped you, please help SO. – JMax Nov 18 '11 at 15:50
1

Just want to point out that you don't have to reference a row as Rows(CStr(InsRowNumber - 1) & ":" & CStr(InsRowNumber - 1)) You can simply say Rows(InsRowNumber-1).Copy – Justin Self Nov 18 '11 at 16:35

excel vba insert row with formatting

4 Answers4

Linked