insert muliple rows, every 4th row
 

I have one column with data down the first 50 rows. I need to insert 3 new
rows for each of the existing rows of data. I failed an attemp using a
formula (mod/row) to sort by 1 and then insert. Does anyone know an easier
way to insure 3 new rows?
Thnx!

insert muliple rows, every 4th row
 

I found a macro from searhing other postings in "General Questions"; thanks
D.Peterson:-) Below is macro previously posted and worked great! I can adjust
the amount of inserted rows, which is EXACTLY what I needed. However, just as
a precautionary, how do I "un-do" the macro if I make a mistake? Or reverse
to delete rows? Thnx, ~k

Sub InsertRows()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
r = Cells(Rows.Count, "A").End(xlUp).Row
numRows = 3
For r = r To 1 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert
Next r
Application.ScreenUpdating = True
End Sub


"davisk" wrote:

I have one column with data down the first 50 rows. I need to insert 3 new
rows for each of the existing rows of data. I failed an attemp using a
formula (mod/row) to sort by 1 and then insert. Does anyone know an easier
way to insure 3 new rows?
Thnx!

insert muliple rows, every 4th row
 

Hi

Changes made by macros can not be "un-done".

//Per

"davisk" skrev i meddelelsen
...
I found a macro from searhing other postings in "General Questions"; thanks
D.Peterson:-) Below is macro previously posted and worked great! I can
adjust
the amount of inserted rows, which is EXACTLY what I needed. However, just
as
a precautionary, how do I "un-do" the macro if I make a mistake? Or
reverse
to delete rows? Thnx, ~k

Sub InsertRows()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
r = Cells(Rows.Count, "A").End(xlUp).Row
numRows = 3
For r = r To 1 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert
Next r
Application.ScreenUpdating = True
End Sub


"davisk" wrote:

I have one column with data down the first 50 rows. I need to insert 3
new
rows for each of the existing rows of data. I failed an attemp using a
formula (mod/row) to sort by 1 and then insert. Does anyone know an
easier
way to insure 3 new rows?
Thnx!

insert muliple rows, every 4th row
 

You cannot..you will have to undo (in this case delete these rows) using
another macro...

If this post helps click Yes
---------------
Jacob Skaria


"davisk" wrote:

I found a macro from searhing other postings in "General Questions"; thanks
D.Peterson:-) Below is macro previously posted and worked great! I can adjust
the amount of inserted rows, which is EXACTLY what I needed. However, just as
a precautionary, how do I "un-do" the macro if I make a mistake? Or reverse
to delete rows? Thnx, ~k

Sub InsertRows()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
r = Cells(Rows.Count, "A").End(xlUp).Row
numRows = 3
For r = r To 1 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert
Next r
Application.ScreenUpdating = True
End Sub


"davisk" wrote:

I have one column with data down the first 50 rows. I need to insert 3 new
rows for each of the existing rows of data. I failed an attemp using a
formula (mod/row) to sort by 1 and then insert. Does anyone know an easier
way to insure 3 new rows?
Thnx!

insert muliple rows, every 4th row
 

Understand the can't "un-do". If there is a macro to insert would there not
be one to "delete" rows in same pattern? I can work with what I have and
manually delete being extra careful of the use to insert w/the macro. Just
curious if another was available to delete the rows (?)

Thanks again,
~k

"Jacob Skaria" wrote:

You cannot..you will have to undo (in this case delete these rows) using
another macro...

If this post helps click Yes
---------------
Jacob Skaria


"davisk" wrote:

I found a macro from searhing other postings in "General Questions"; thanks
D.Peterson:-) Below is macro previously posted and worked great! I can adjust
the amount of inserted rows, which is EXACTLY what I needed. However, just as
a precautionary, how do I "un-do" the macro if I make a mistake? Or reverse
to delete rows? Thnx, ~k

Sub InsertRows()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
r = Cells(Rows.Count, "A").End(xlUp).Row
numRows = 3
For r = r To 1 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert
Next r
Application.ScreenUpdating = True
End Sub


"davisk" wrote:

I have one column with data down the first 50 rows. I need to insert 3 new
rows for each of the existing rows of data. I failed an attemp using a
formula (mod/row) to sort by 1 and then insert. Does anyone know an easier
way to insure 3 new rows?
Thnx!

insert muliple rows, every 4th row
 

You can use another macro such as the below to delete all blank rows

Sub DeleteEmptyrows()
For lngRow = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step-1

If WorksheetFunction.CountBlank(Rows(lngRow)) = Columns.Count _
Then Rows(lngRow).Delete

Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"davisk" wrote:

Understand the can't "un-do". If there is a macro to insert would there not
be one to "delete" rows in same pattern? I can work with what I have and
manually delete being extra careful of the use to insert w/the macro. Just
curious if another was available to delete the rows (?)

Thanks again,
~k

"Jacob Skaria" wrote:

You cannot..you will have to undo (in this case delete these rows) using
another macro...

If this post helps click Yes
---------------
Jacob Skaria


"davisk" wrote:

I found a macro from searhing other postings in "General Questions"; thanks
D.Peterson:-) Below is macro previously posted and worked great! I can adjust
the amount of inserted rows, which is EXACTLY what I needed. However, just as
a precautionary, how do I "un-do" the macro if I make a mistake? Or reverse
to delete rows? Thnx, ~k

Sub InsertRows()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
r = Cells(Rows.Count, "A").End(xlUp).Row
numRows = 3
For r = r To 1 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert
Next r
Application.ScreenUpdating = True
End Sub


"davisk" wrote:

I have one column with data down the first 50 rows. I need to insert 3 new
rows for each of the existing rows of data. I failed an attemp using a
formula (mod/row) to sort by 1 and then insert. Does anyone know an easier
way to insure 3 new rows?
Thnx!

insert muliple rows, every 4th row
 

You'll have to keep track of everything yourself--not for the faint-hearted.

John Walkenbach has some notes:
http://spreadsheetpage.com/index.php...ba_subroutine/

Me...

I save my file before running (sometimes as a new name). Then I can close
without saving if I want to get things back to the way they were.

In testing mode, I'll make several copies of the test sheet and destroy each
with the code. I'll make more copies for more testing.

davisk wrote:

I found a macro from searhing other postings in "General Questions"; thanks
D.Peterson:-) Below is macro previously posted and worked great! I can adjust
the amount of inserted rows, which is EXACTLY what I needed. However, just as
a precautionary, how do I "un-do" the macro if I make a mistake? Or reverse
to delete rows? Thnx, ~k

Sub InsertRows()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
r = Cells(Rows.Count, "A").End(xlUp).Row
numRows = 3
For r = r To 1 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert
Next r
Application.ScreenUpdating = True
End Sub

"davisk" wrote:

I have one column with data down the first 50 rows. I need to insert 3 new
rows for each of the existing rows of data. I failed an attemp using a
formula (mod/row) to sort by 1 and then insert. Does anyone know an easier
way to insure 3 new rows?
Thnx!




--

Dave Peterson

insert muliple rows, every 4th row
 

ps. I bet that this was a response to an existing post. If I wrote it myself,
I'd qualify the ranges and use "as long" instead of "as integer":

Option Explicit
Sub InsertRows()

Dim numRows As Long
Dim r As Long

Application.ScreenUpdating = False

With ActiveSheet
r = .Cells(.Rows.Count, "A").End(xlUp).Row
numRows = 3
For r = r To 1 Step -1
.Rows(r + 1).Resize(numRows).Insert
Next r
End With

Application.ScreenUpdating = True

End Sub


davisk wrote:

I found a macro from searhing other postings in "General Questions"; thanks
D.Peterson:-) Below is macro previously posted and worked great! I can adjust
the amount of inserted rows, which is EXACTLY what I needed. However, just as
a precautionary, how do I "un-do" the macro if I make a mistake? Or reverse
to delete rows? Thnx, ~k

Sub InsertRows()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
r = Cells(Rows.Count, "A").End(xlUp).Row
numRows = 3
For r = r To 1 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows).Insert
Next r
Application.ScreenUpdating = True
End Sub

"davisk" wrote:

I have one column with data down the first 50 rows. I need to insert 3 new
rows for each of the existing rows of data. I failed an attemp using a
formula (mod/row) to sort by 1 and then insert. Does anyone know an easier
way to insure 3 new rows?
Thnx!




--

Dave Peterson