I am sent a continuous range of client IDs for a report every month that will
vary in length each month. In between each client ID I need to insert 6
empty rows. Does anyone know how this can be done through the use of a
macro? I don't know how to get excel to put 6 rows between each original
cell for the full range of data that will change each month. Any help would
be greatly appreciated

Hi,

I've assumed these ID's are in columnA. Right click your sheet tab, view
code and paste this in and run it

Sub Prime_Lending()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For x = lastrow To 2 Step -1
If Cells(x, 1).Value < Cells(x - 1, 1).Value Then
For i = 1 To 6
Rows(x).Insert Shift:=xlUp
Next
End If
Next
End Sub

Mike

"dschindler" wrote:

I am sent a continuous range of client IDs for a report every month that will
vary in length each month. In between each client ID I need to insert 6
empty rows. Does anyone know how this can be done through the use of a
macro? I don't know how to get excel to put 6 rows between each original
cell for the full range of data that will change each month. Any help would
be greatly appreciated

Assuming a header row:

Sub lines()
Dim lr As Long, sh As Worksheet
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set sh = ActiveSheet
For i = lr To 2 Step -1
Cells(i + 1, 1).Resize(6, 1).EntireRow.Insert
Next
End Sub




"dschindler" wrote:

I am sent a continuous range of client IDs for a report every month that will
vary in length each month. In between each client ID I need to insert 6
empty rows. Does anyone know how this can be done through the use of a
macro? I don't know how to get excel to put 6 rows between each original
cell for the full range of data that will change each month. Any help would
be greatly appreciated

That's awesome, thanks!!!

"Mike H" wrote:

Hi,

I've assumed these ID's are in columnA. Right click your sheet tab, view
code and paste this in and run it

Sub Prime_Lending()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For x = lastrow To 2 Step -1
If Cells(x, 1).Value < Cells(x - 1, 1).Value Then
For i = 1 To 6
Rows(x).Insert Shift:=xlUp
Next
End If
Next
End Sub

Mike

"dschindler" wrote:

I am sent a continuous range of client IDs for a report every month that will
vary in length each month. In between each client ID I need to insert 6
empty rows. Does anyone know how this can be done through the use of a
macro? I don't know how to get excel to put 6 rows between each original
cell for the full range of data that will change each month. Any help would
be greatly appreciated