Excel vba formatted insertion string

Question

I have a macro that inserts several rows depending on user input in Excel 2007. Everything works, but I still have a little problem. I want to copy the full formatting from the line above. It only works for some cells in a row.

Here is the code to insert:

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

Is it possible to change it?

best usa

Answer 1

I don’t know exactly how you insert your lines, but if you enter a line below the existing line, it will default to the formatting of the line above it, at least when you use this type of syntax:

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

In this example, he will insert a line below B2, and the format (say, line B2 is highlighted in yellow) will also be yellow. This may be due to the fact that this type of insert determines exactly which row to insert.

Answer 2

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 

Answer 3

 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