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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|