PDA

View Full Version : Solved: Can this code be made any more efficient?



sassora
03-21-2008, 10:00 AM
I made this function to insert a row, every so many rows. Can it be polished more? :*)


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

Bob Phillips
03-21-2008, 10:27 AM
3% faster



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

mdmackillop
03-21-2008, 11:03 AM
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

For i = FirstRowInserted To LastRowInserted Step Spacing
Cells(i + j, 1).EntireRow.Insert
j = j + 1
Next i

sassora
03-21-2008, 12:23 PM
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
03-21-2008, 01:31 PM
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
03-21-2008, 03:44 PM
We loop backwards to dispense with the j counter.

sassora
03-21-2008, 05:18 PM
That's sneaky but I like it

sassora
03-22-2008, 03:11 AM
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
03-22-2008, 03:14 AM
Use an InputBox to get the values to keep your code flexible.

sassora
03-22-2008, 03:18 AM
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
03-22-2008, 03:39 AM
Using XLD's code, here's an input method. Some checking/error handling would be advisable.

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