lines
 

hi,

lets say i have a sheet with like 40 rows in it. i want to group the rows
by account number. so i am curious if there is any code i could write to
group the account numbers, with a row between each group, and to see if there
is code i can write to draw a dotted line between each group.

thanks in advance,
geebee

lines
 

geebee, Copy the code below and paste it in a standard module.
Assumptions:
1. Account numbers are in column A
2. Row 1 is a header row
3. There are no blank rows in column A
4. The data is already sorted by column A
Does that help? James

Sub BreakAcctNbrs()
Dim k As Long, LastCol As Integer
LastCol = Cells(1, "iv").End(xlToLeft).Column
For k = Cells(Rows.Count, "a").End(xlUp).Row To 3 Step -1
If Cells(k, "a") < Cells(k - 1, "a") _
And Cells(k, "a") < "" And Cells(k - 1, "a") < "" Then
Rows(k).EntireRow.Insert
With Range(Cells(k, "a"), _
Cells(k, LastCol)).Borders(xlEdgeBottom)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End If
Next k
End Sub

"geebee" (noSPAMs) wrote in message
...
hi,

lets say i have a sheet with like 40 rows in it. i want to group the rows
by account number. so i am curious if there is any code i could write to
group the account numbers, with a row between each group, and to see if
there
is code i can write to draw a dotted line between each group.

thanks in advance,
geebee

lines
 

thanks. pretty slick. could you annotate the code. so that i can know what
each line is doing. im still learning VBA. and want to mke sure i understad
what each line is doing.



"Zone" wrote:

geebee, Copy the code below and paste it in a standard module.
Assumptions:
1. Account numbers are in column A
2. Row 1 is a header row
3. There are no blank rows in column A
4. The data is already sorted by column A
Does that help? James

Sub BreakAcctNbrs()
Dim k As Long, LastCol As Integer
LastCol = Cells(1, "iv").End(xlToLeft).Column
For k = Cells(Rows.Count, "a").End(xlUp).Row To 3 Step -1
If Cells(k, "a") < Cells(k - 1, "a") _
And Cells(k, "a") < "" And Cells(k - 1, "a") < "" Then
Rows(k).EntireRow.Insert
With Range(Cells(k, "a"), _
Cells(k, LastCol)).Borders(xlEdgeBottom)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End If
Next k
End Sub

"geebee" (noSPAMs) wrote in message
...
hi,

lets say i have a sheet with like 40 rows in it. i want to group the rows
by account number. so i am curious if there is any code i could write to
group the account numbers, with a row between each group, and to see if
there
is code i can write to draw a dotted line between each group.

thanks in advance,
geebee

lines
 

Ok, here you go.

Sub BreakAcctNbrs()
'Subroutine written on Excel 2003.
'Not intended for Excel 2007. Might work for Excel 2007
'if no columns past 256 (column IV) are used
'and no rows below 65536 are used.

'It is not necessary to dim variables unless you are using Option
Explicit
'However, it is a good idea to use Option Explicit to avoid similar
names
'like myvar, MyVar and myVar conflicting with each other.

'The remaining remarks refer to the line below the remark.

'Since k will refer to rows, a long type variable is needed.
'Since LastCol will refer to columns, an integer type is sufficient.
Dim k As Long, LastCol As Integer
'Since the heading row will probably end at the last used column,
'determine the last column in the heading row to see how far across
'the sheet the dashed line should go.
LastCol = Cells(1, "iv").End(xlToLeft).Column
'The following loop will be used to insert blank rows and dashed lines
'between account numbers. When inserting rows, always go from the
bottom
'of the sheet upward.
'Since row 1 is a heading row and the code in the loop looks at the
'line before the line referred to by k, it is only necessary to loop
'k up to 3.
For k = Cells(Rows.Count, "a").End(xlUp).Row To 3 Step -1
'if the cell in column A of the current row (row k) is different
'than the cell in column A of the row above, then insert a row
'and a dashed line. Check that column A isn't empty to avoid
'inserting unneeded rows on subequent runs.
If Cells(k, "a") < Cells(k - 1, "a") _
And Cells(k, "a") < "" And Cells(k - 1, "a") < "" Then
'insert a blank row above row k.
Rows(k).EntireRow.Insert
'Using the range statement variation Range(Cells,Cells)
'is handy to put the dashed line from column A to the last
'used column.
With Range(Cells(k, "a"), _
Cells(k, LastCol)).Borders(xlEdgeBottom)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End If
Next k
End Sub

"geebee" (noSPAMs) wrote in message
...
thanks. pretty slick. could you annotate the code. so that i can know
what
each line is doing. im still learning VBA. and want to mke sure i
understad
what each line is doing.



"Zone" wrote:

geebee, Copy the code below and paste it in a standard module.
Assumptions:
1. Account numbers are in column A
2. Row 1 is a header row
3. There are no blank rows in column A
4. The data is already sorted by column A
Does that help? James

Sub BreakAcctNbrs()
Dim k As Long, LastCol As Integer
LastCol = Cells(1, "iv").End(xlToLeft).Column
For k = Cells(Rows.Count, "a").End(xlUp).Row To 3 Step -1
If Cells(k, "a") < Cells(k - 1, "a") _
And Cells(k, "a") < "" And Cells(k - 1, "a") < "" Then
Rows(k).EntireRow.Insert
With Range(Cells(k, "a"), _
Cells(k, LastCol)).Borders(xlEdgeBottom)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End If
Next k
End Sub

"geebee" (noSPAMs) wrote in message
...
hi,

lets say i have a sheet with like 40 rows in it. i want to group the
rows
by account number. so i am curious if there is any code i could write
to
group the account numbers, with a row between each group, and to see if
there
is code i can write to draw a dotted line between each group.

thanks in advance,
geebee