Life will be lots easier if you start at the bottom and work up the range.

Same thing when you're deleting rows, too:

Option Explicit
Sub testme()
Dim RowNdx As Long
Dim Arr As Variant
Dim StartRow As Long
Dim EndRow As Long
Application.ScreenUpdating = False
StartRow = 1
EndRow = Cells(Rows.Count, "A").End(xlUp).Row
Arr = Application.Transpose(Array("Hair Service", "Hair Retail", _
"Total Hair", "Beauty Service", "Beauty Retail", _
"Total Beauty", "Total", "Colour Number", "Treatment Number", _
"Facial Number", "Waxing Number", "Hair Service Customer No", _
"Beauty Service Customer No", "Hair CF Count", "Beauty CF Count", ""))
For RowNdx = EndRow To StartRow + 1 Step -1
Rows(RowNdx).Resize(16).Insert
Cells(RowNdx, 1).Resize(16, 1).Value = Arr
Next RowNdx
Application.ScreenUpdating = True
End Sub



wrote:

Hi Guys,

Hust a little more info.

The macro below works fine until the number of rows increases. I've
tried it with up to 15 rows and it works fine, anything over that and
it doesn't finish the range. It seems the more rows, the more it leaves
untouched, but I can't see a pattern. With 16 rows it leaves 1 not done
and the same with 30 rows. With 50 rows it leaves 3, but with 400 rows
it leaves 23 not done?!?

Dim RowNdx As Long
Dim Arr As Variant
Dim StartRow As Long
Dim EndRow As Long
Application.ScreenUpdating = False
StartRow = 1
EndRow = Cells(Rows.Count, "A").End(xlUp).Row
Arr = Application.Transpose(Array("Hair Service", "Hair Retail", "Total

Hair", "Beauty Service", "Beauty Retail", "Total Beauty", "Total",
"Colour Number", "Treatment Number", "Facial Number", "Waxing Number",
"Hair Service Customer No", "Beauty Service Customer No", "Hair CF
Count", "Beauty CF Count", ""))
For RowNdx = StartRow + 1 To (EndRow) * 16 Step 17
Rows(RowNdx).Resize(16).Insert
Cells(RowNdx, 1).Resize(16, 1).Value = Arr
Next RowNdx

--

Dave Peterson