Adding Lines automatically

  • Thread starter Thread starter Brad
  • Start date Start date
B

Brad

I have hundreds and hundreds of products already sorted in
a spreadsheet, and I was wondering if anyone knows how to
insert (lets say 4 or 5) lines in between each product (if
sorted by product name) automatically. I understand that
you could go one by one w/all the products and insert them
in manually. But is there anyways to do that on excel or
anyway to trick the program and exercising this function.
If someone could get back to me if they know, that would
be great.
Thanks
 
There's probably a much easier way but this macro should do it. Select the
first cell in the column that contains the product names (make sure this is
in row 2 or greater otherwise there is no previous value to compare to) and
run the macro.

Sub InsRows()
Dim EndRow As Long
Dim n As Long
Dim col As Integer

If ActiveCell.Row > 1 Then

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

col = ActiveCell.Column
EndRow = ActiveCell.End(xlDown).Row

n = ActiveCell.Row
Do Until n = EndRow
If Cells(n, col) <> Cells(n - 1, col) And Cells(n - 1, col) <>
"" Then
Range(Cells(n, 1), Cells(n + 5, 1)).EntireRow.Insert
n = n + 5
EndRow = EndRow + 6
End If
n = n + 1
Loop

Application.Calculation = xlCalculationAutomatic
End If

End Sub
 
Brad

"Automatic" ususally means via a VBA macro. The code below inserts five rows
at each change in product name in your selected column. Make sure you sort
first to get names together.

You may wish to make a copy of your workbook before attempting...........

Sub InsertRow_At_Change()
Dim i As Integer
For i = Selection.Rows.Count To 1 Step -1
If Selection(i).Row = 1 Then Exit Sub
If Selection(i) <> Selection(i - 1) And Not IsEmpty _
(Selection(i - 1)) Then
With Selection(i).Resize(5, 1)''change the 5 for more or less
.EntireRow.Insert
End With
End If
Next
End Sub

With your workbook open, hit ALT + F11 to open the Visual Basic Editor.
View>Project Explorer.

Select your workbook project and Insert>Module. Copy/paste the code into
here.

ALT + Q to go back to Excel. Select your products column and ALT + F8 to open
Macros dialog box. You will see the "InsertRow_At_Change" macro. Click
"Run".

Gord Dibben Excel MVP - XL97 SR2 & XL2002
 
Back
Top