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" wrote:

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 wrote:

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

--

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 wrote:

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" wrote:

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 wrote:

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

--

Dave Peterson


--

Dave Peterson

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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

--

Dave Peterson


--

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!

SteveDB1 wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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" wrote:

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

SteveDB1 wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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

--

Dave Peterson


--

Dave Peterson


--

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

SteveDB1 wrote:

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" wrote:

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

SteveDB1 wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson