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

  • Thread starter Thread starter dschindler
  • Start date Start date
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
 
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
 
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
 
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...
 
Back
Top