how to put a line between each group?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My file has over 1000 lines by sku & customr, I need to add line between each
sku group. This is a data file, I don't want to pivot table to add line,
instead of manually add line between each sku, does excel have a formular to
add line?
 
If they are sorted, you can use the Data->Subtotals command. It won't put in
a blank line though - it'll add a new line with a formula
 
Excel has no formula to format rows or columns, but a simple macro will do it.
Press Alt-F11 to open the macro editor, click on the sheet name on the left
pane and copy this code on the main pane. To run the macro, press ALT-F8 and
double-click on AddLineAfterEachSKU.

You must adapt two lines of codes to your needs. Obviously, it is expected
that your sheet is sorted by SKU and that there are no blank lines.

Sub AddLineAfterEachSKU()

Dim sColumn As String
Dim iFirstRow As Long
Dim i As Long

' CHANGE THE LINE BELOW
' AND PUT THE COLUMN NAME
' THAT CONTAINS THE SKU CODE
sColumn = "A"

' CHANGE THE LINE BELOW
' AND PUT THE ROW NUMBER
' THAT CONTAINS THE FIRST SKU CODE
' (THIS TO SKIP THE HEADER LINES)
iFirstRow = 2

i = iFirstRow + 1
While Cells(i, sColumn) <> ""
If Cells(i - 1, sColumn) <> Cells(i, sColumn) Then
Rows(i).Select
Selection.Insert Shift:=xlDown
i = i + 2
Else
i = i + 1
End If
Wend

End Sub

Stephane Quenson.
 
"Line" means a "row"?

You can do this with a macro but not a formula.

Formulas can only return values.

Here is a macro that inserts a blank row at every change in value in column A

Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim x As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False

For x = LastRow To 3 Step -1
If Cells(x, 1).Value <> Cells(x - 1, 1).Value Then
If Cells(x, 1).Value <> "" Then
If Cells(x - 1, 1).Value <> "" Then
Cells(x, 1).EntireRow.Insert Shift:=xlDown
End If
End If
End If
Next x
Application.ScreenUpdating = True
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP
 
My file has over 1000 lines by sku & customr, I need to add line
between each sku group. This is a data file, I don't want to pivot
table to add line, instead of manually add line between each sku, does
excel have a formular to add line?
Click to expand...

This isn't a formula, but conditional formatting might help.

Select the whole sheet.

Then
Format > Conditional formatting

In the Condition 1 dialog box,
Choose "Formula is" in the pull-down list
The following formula (assuming column A has the SKU value):
=$A1<>$A2
Click "Format" and choose a single underline only

Modify to suit.
 
Actually I need to physically add a line (not a blank row) between a group of
skus, see below, I need a line under AAA Rich, another line under BBB Scott.

SKU Customer
AAA Jack
AAA Scott
AAA Rich
BBB Scott
 
Great!

Thank You!

MyVeryOwnSelf said:
This isn't a formula, but conditional formatting might help.

Select the whole sheet.

Then
Format > Conditional formatting

In the Condition 1 dialog box,
Choose "Formula is" in the pull-down list
The following formula (assuming column A has the SKU value):
=$A1<>$A2
Click "Format" and choose a single underline only

Modify to suit.
Click to expand...
 
Back
Top