Insert a fixed number of lines between a varying range of cells

D

dschindler

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
 
M

Mike H

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
 
J

JLGWhiz

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
 
D

dschindler

That's awesome, thanks!!!

Mike H said:
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
Click to expand...
 
Top