insert rows by vba

[SteveDB1]

Hi all.
Hope everyone is enjoying their respective memorial day weekend.

I have a large file that I've made, and want to insert a series of rows by
vba.
I thought the following would work, but I've missed something, and I cannot
quite figure out where I've gone wrong.
I've tried a number of variations, and nothing works. I.e., they'll either
only insert one row, but no more, or no rows at all.
I'd thought that some variation of:

for i = 1 to n step X would work, but it hasn't.

Sub InsertRow()
'
' InsertRow Macro
' make this a auto-insert macro to insert my rows for me.
' it'll definitely save time.

Dim MyRange As Range

Set MyRange = Nothing

On Error Resume Next
Set MyRange = Application.InputBox(Prompt:="Pick the row to start at.",
Type:=8).Rows(1)

Workbooks.Application.ScreenUpdating = False
For i = 1 To 4 Step 8

MyRange(i).EntireRow.Insert (xlShiftDown)

Next i

'On Error GoTo 0
'If MyRange Is Nothing Then
'Exit Sub 'user hit cancel.
'End If




Savechanges = True


End Sub

 

[SteveDB1]

I believe that I've solved part of my problem. I'd forgotten that it counts
one row at a time-- in my case-- and had not reached the next row group that
I wanted to insert. As such I set my 4 to 20 and it inserted 3 rows.
Along that line, I noticed with my last inserted row, it was up from my
insert point by one row* .
I.e., I have 8 rows with values in them, 3, 6, 9, 12, 15, 18, 21, 24.
For each time I reach a 3, I want to insert a row above it; thus putting a
space between the 24, and next 3.
As stated above (*), it placed a row in between 21, and 24.

What code would I use to ensure that I always am between the 24, and the
next 3?
e.g.,

24

3
6
9
12
15
18
21
24

3 etc...

Thanks again for your helps.

 

[Dave Peterson]

When you're inserting/deleting rows, it sometimes make life much easier to work
from the bottom up:

This:
For i = 1 To 40 Step 8

Becomes:
For i = 40 to 1 step -8

I'm not sure how that fits into the rest of your code, though.

Maybe...

Sub InsertRow()
Dim MyRange As Range
dim i as long

Set MyRange = Nothing
On Error Resume Next
Set MyRange = Application.InputBox(Prompt:="Pick the row to start at.", _
Type:=8).Rows(1)
on error goto 0

if myRange is nothing then
exit sub
end if

Workbooks.Application.ScreenUpdating = False
For i = myrange.row To 4 Step -8
rows(i).EntireRow.Insert
Next i

End Sub

 

[SteveDB1]

Dave,
When you say it "makes life easier..."
is that because when it goes forward the rows start getting offset in an
increasing count from the count originally showing? I.e., once I figured out
why I wasn't getting it to work, I increased my 'to' from 4 to 20, and by the
last one it'd jumped up one from the 24-3 spacing I wanted to a 21-24
spacing. (I hope that makes sense.)

I've changed it to go backwards, to see how it works. I'll let you know once
it finishes the initial run. It looks as though it'll be a while as my home
system is 8 years old, and runs likes a turtle, snail's pace.

 

[Dave Peterson]

Yep.

When you insert/delete a row, your code has to take that in consideration.

When you start from the bottom and work up, you're past the row (with a smaller
row number), so you don't have to care.

 

[SteveDB1]

Just wanted to get back to you to let you know that this works.
Thank you.
Now if I can just figure out how to make an old laptop work faster.....
This macro has shown me just how old my 8 yr old laptop really is.
Again-- thanks.
Best.

 

[Dave Peterson]

If you can just get up and go get a few(!) cups of coffee, then at least the
time won't be wasted!

Just wanted to get back to you to let you know that this works.
Thank you.
Now if I can just figure out how to make an old laptop work faster.....
This macro has shown me just how old my 8 yr old laptop really is.
Again-- thanks.
Best.

 

[SteveDB1]

lmao....
a few cups isn't the issue-- I left it on last night, so it ran all night,
and was running still when I left for work this morning. I can only HOPE that
it's done by the time I get home in 45 minutes. But, I suppose on some level,
I should still be grateful that I'm not the one inserting rows 20 at a time--
there are 27000 lines in this worksheet, so that would've taken me a long,
long, long time.......
Thanks again.

 

[Dave Peterson]

There are somethings that can improve the performance (maybe...):

Saved from a previous post:

If you turn off the page break lines
tools|options|view tab|uncheck page breaks (in xl2003)

Does the code work faster?

When you're hiding rows/columns, excel wants to figure out where to draw those
lines each time you change the layout.

Turning of calculation and making sure you're in Normal view will help, too.

Option Explicit
Sub testme()

Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'your real code here

'put things back to what they were
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
ActiveWindow.View = ViewMode

End Sub

lmao....
a few cups isn't the issue-- I left it on last night, so it ran all night,
and was running still when I left for work this morning. I can only HOPE that
it's done by the time I get home in 45 minutes. But, I suppose on some level,
I should still be grateful that I'm not the one inserting rows 20 at a time--
there are 27000 lines in this worksheet, so that would've taken me a long,
long, long time.......
Thanks again.