PDA

View Full Version : Solved: Insert Row every nth row



kathyb0527
11-09-2009, 01:35 PM
I have a large amount of data (>5000 rows) that I need to copy and paste into word (as a picture). In order to do this, I need to break up the data into manageable tables (~32 rows) that will fit on a page. I've tried variations on the code below, but I get too many Title rows and I end up with too many tables in my document.

Sub Insert_Row()
Dim a As Byte
Dim c As Integer
Range("A1").Select
a = 30
c = 0

While ActiveCell.Value <> ""

c = c + 32
Rows("1:1").Copy
ActiveSheet.Rows(c).Insert Shift:=xlDown
ActiveSheet.Rows(c).Insert Shift:=xlDown
ActiveCell.Offset(a, 0).Select
Wend
End Sub


If anyone can alter this code or shove me in the right direction I'd appreciate it.

Thanks!

Bob Phillips
11-09-2009, 02:06 PM
Sub Insert_Row()
Dim LastRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow - LastRow Mod 32 To 32 Step -32

.Rows(i + 1).Insert
Next i
End With
End Sub

mdmackillop
11-10-2009, 08:40 AM
The general rule for inserting or deleting rows is to start at the bottom, otherwise any Looping codes tend to go wrong.

kathyb0527
11-10-2009, 01:58 PM
Thanks for the code and the advice!