Solved: Can this code be made any more efficient?

[sassora]

I made this function to insert a row, every so many rows. Can it be polished more?

[VBA]
Function InsertRow(FirstRowInserted As Integer, LastRowInserted As Integer, Spacing As Integer)
Dim i As Integer
Dim j As Integer
j = -1
For i = FirstRowInserted To LastRowInserted Step Spacing
j = j + 1
Cells(i + j, 1).EntireRow.Insert
Next i
End Function
[/VBA]

[Bob Phillips]

3% faster

[vba]

Function InsertRow(FirstRowInserted As Long, LastRowInserted As Long, Spacing As Long)
Dim i As Long
For i = LastRowInserted - ((LastRowInserted - (FirstRowInserted - 1)) Mod Spacing) To FirstRowInserted Step -Spacing
Rows(i + 1).Insert
Next i
Rows(i).Insert
End Function
[/vba]

[mdmackillop]

I won't try to improve on Bob's other than to suggest you use ScreenUpdating.

General comments
Use Long instead of integer; Excel converts to long anyway.

Functions are used to return values, so a Sub can be used here.

Instead of setting j = -1, increment after the action
[vba]
For i = FirstRowInserted To LastRowInserted Step Spacing
Cells(i + j, 1).EntireRow.Insert
j = j + 1
Next i
[/vba]

[sassora]

Thanks

I put it in a function to tidy my code and make it easy for my colleagues who program only SQL to make changes.

The comment about j = -1 is good.

It seems strange that looping backwards is faster than going forwards!
Could the improvement be through using "Rows(i).Insert" instead of "Cells(i + j, 1).EntireRow.Insert"?

[mdmackillop]

Thje reason to loop backwards is not for speed. if you want to add spacing to items Data1 - Data100, the last value will not be at Row 100 when the code stops. Starting at the end avoids this problem.
Similarly, deleting rows should also be done from the end.

[Bob Phillips]

We loop backwards to dispense with the j counter.

[sassora]

That's sneaky but I like it

[sassora]

I want to now be able to apply the same sort of thinking to develop a procedure that can insert spaces to group things in 3's say and then an extra space after every 6 lines say.

It would have to take in account the extra lines produced when inserting a line every 3 rows.

[mdmackillop]

Use an InputBox to get the values to keep your code flexible.

[sassora]

I was looking to insert the first and last rows before any inserts and then for it to know the rest. Otherwise it takes a lot of thought to think where the rows might be inserted.

[mdmackillop]

Using XLD's code, here's an input method. Some checking/error handling would be advisable.
[vba]
Sub InsertRow()
Dim i As Long
Dim FirstRowInserted As Long, LastRowInserted As Long, Spacing As Long
Dim Data As String

Data = InputBox("Insert parameters", "Spacing Parameters", "Start End Spacing")
FirstRowInserted = Split(Data)(0)
LastRowInserted = Split(Data)(1)
Spacing = Split(Data)(2)

For i = LastRowInserted - ((LastRowInserted - (FirstRowInserted - 1)) Mod Spacing) To FirstRowInserted Step -Spacing
Rows(i + 1).Insert
Next i
Rows(i).Insert
End Sub

[/vba]