A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

insert muliple rows, every 4th row



 
 
Thread Tools Display Modes
  #1  
Old June 18th, 2009, 05:44 AM posted to microsoft.public.excel.worksheet.functions
davisk
external usenet poster
 
Posts: 14
Default 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!



Ads
  #2  
Old June 18th, 2009, 08:02 AM posted to microsoft.public.excel.worksheet.functions
davisk
external usenet poster
 
Posts: 14
Default 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!



  #3  
Old June 18th, 2009, 08:25 AM posted to microsoft.public.excel.worksheet.functions
Per Jessen
external usenet poster
 
Posts: 686
Default 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!



  #4  
Old June 18th, 2009, 08:45 AM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default 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!



  #5  
Old June 18th, 2009, 12:32 PM posted to microsoft.public.excel.worksheet.functions
davisk
external usenet poster
 
Posts: 14
Default 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!



  #6  
Old June 18th, 2009, 01:17 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default 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!



  #7  
Old June 18th, 2009, 01:39 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 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
  #8  
Old June 18th, 2009, 01:41 PM posted to microsoft.public.excel.worksheet.functions
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:29 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.