insert muliple rows, every 4th row

D

davisk

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!
 
D

davisk

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
 
J

Jacob Skaria

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

If this post helps click Yes
 
D

davisk

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
 
J

Jacob Skaria

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
 
D

Dave Peterson

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

John Walkenbach has some notes:
http://spreadsheetpage.com/index.php/tip/undoing_a_vba_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.
 
D

Dave Peterson

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top