Insert rows fails

[jwburritt]

Hello: I've counted rows in file1 and want to insert 2.25 rows in file2. Everything is fine until row insert statement. I get the error:

Insert method of range class failed.

I've tried a ton of other iterations and can't get it to work.

Any help would be greatly appreciated!

Dim wb as Workbook
Set wb = Workbooks("File1.xlsm")

Dim RowCount As Long
RowCount = wb.Worksheets("New Sheet").Range("B6").CurrentRegion.Rows.Count

Dim wb2 As Workbook
Set wb2 = Workbooks("File2.xlsm")

wb2.Worksheets("Data").Range("C10").EntireRow.Resize(RowCount * 2.25).Insert Shift:=xlUp

 

[MARK858]

What do you mean by insert 2.25 rows? EntireRow means exactly that (the entire row) and you can only Resize by whole numbers.

 

[jwburritt]

Hello: RowCount is a variable that I multiply by 2.25 to give the entire integer for Resize. I've substituted a whole number and still get the failure.

 

[MARK858]

First of all if for instance your RowCount was 11 and you multiplied it by 2.25 you wouldn't end up with an Integer, you would end up with 24.75.

Second when I run

Sub ggggg()
Worksheets("Data").Range("C10").EntireRow.Resize([COLOR="#FF0000"][B]6[/B][/COLOR]).Insert Shift:=xlUp
End Sub

it inserts 6 rows as expected so if you are still getting an error with a whole number in the Resize the first questions are do you have any sheet protection? or merged cells?

 

[Twollaston]

I'm not sure I understand the code you put together, i've never tried inputting partial row. Have you tried, recording a macro while you complete the action of inserting 2 rows, and then testing that in a seperate macro? I usually record my self doing something when I'm having a problem with the code.

this worked for me

    ActiveSheet.Rows("10:10").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveSheet.Rows("11:11").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

 

[jwburritt]

Nope. No protection or merged cells. My feeling is it has something to do with the syntax of the statement. But cant figure it iu.t

 

[MARK858]

The syntax is fine, as I stated the below works fine for me (it even works with Resize(6.75), it just inserts 7 rows rather than 6).

Sub ggggg()
Worksheets("Data").Range("C10").EntireRow.Resize(6).Insert Shift:=xlUp
End Sub

Open wb2 put the code above in that workbook and run, what happens?

 

[jwburritt]

Thanks so much! I realized that I had moved a line of code that was copying a range and was active. That help up the row insert. Moved the line of copy code and it works as expected.

Thanks also for the row.count. I changed it so that it always returns a whole number.

Thanks again!