is there a code to go through by rows and find when a certain cell border
comes up? For example, if i have rows lumped in groups of three, and each
set of three is seperated by the double line border.

is there a way to tell it to group (Datat--Group) these three together
based on the fact that they are withing this border?

for example, (i would not know how to program this)

search row by row
when it finds the double line, start grouping.
when it finds the double line again, stop that group and start another one

thanks
steve

Try doing this by hand once. You'll may see that all the rows are grouped
together--since there isn't a break between each group.

But this may work for you:

Option Explicit
Sub testme01()

Dim TopCell As Range
Dim BotCell As Range
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
'clean up first.
.Cells.ClearOutline

FirstRow = 2
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row

Set TopCell = .Cells(FirstRow, "A")
Set BotCell = TopCell

For iRow = FirstRow + 1 To LastRow
If .Cells(iRow, "A").Borders(xlEdgeBottom).LineStyle _
= xlDouble Then
Set BotCell = .Cells(iRow, "A")
.Range(TopCell.Offset(1, 0), BotCell).Rows.Group
Set TopCell = BotCell.Offset(1, 0)
Set BotCell = TopCell
End If
Next iRow

If BotCell.Row = LastRow Then
'do nothing
Else
.Range(BotCell.Offset(1, 0), .Cells(LastRow, "a")).Rows.Group
End If

End With

End Sub

steve wrote:

is there a code to go through by rows and find when a certain cell border
comes up? For example, if i have rows lumped in groups of three, and each
set of three is seperated by the double line border.

is there a way to tell it to group (Datat--Group) these three together
based on the fact that they are withing this border?

for example, (i would not know how to program this)

search row by row
when it finds the double line, start grouping.
when it finds the double line again, stop that group and start another one

thanks
steve

--

Dave Peterson

that was awesome man, thanks a lot

"Dave Peterson" wrote:

Try doing this by hand once. You'll may see that all the rows are grouped
together--since there isn't a break between each group.

But this may work for you:

Option Explicit
Sub testme01()

Dim TopCell As Range
Dim BotCell As Range
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
'clean up first.
.Cells.ClearOutline

FirstRow = 2
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row

Set TopCell = .Cells(FirstRow, "A")
Set BotCell = TopCell

For iRow = FirstRow + 1 To LastRow
If .Cells(iRow, "A").Borders(xlEdgeBottom).LineStyle _
= xlDouble Then
Set BotCell = .Cells(iRow, "A")
.Range(TopCell.Offset(1, 0), BotCell).Rows.Group
Set TopCell = BotCell.Offset(1, 0)
Set BotCell = TopCell
End If
Next iRow

If BotCell.Row = LastRow Then
'do nothing
Else
.Range(BotCell.Offset(1, 0), .Cells(LastRow, "a")).Rows.Group
End If

End With

End Sub

steve wrote:

is there a code to go through by rows and find when a certain cell border
comes up? For example, if i have rows lumped in groups of three, and each
set of three is seperated by the double line border.

is there a way to tell it to group (Datat--Group) these three together
based on the fact that they are withing this border?

for example, (i would not know how to program this)

search row by row
when it finds the double line, start grouping.
when it finds the double line again, stop that group and start another one

thanks
steve

--

Dave Peterson

going off the code you gave me.....

the grouping part worked perfect. i also have sort buttons with the code
you gave me worked into them. the issue is that the double lines do not
move. i'm working on a way that it places the double line in the rows where
the formulas change. this is because each job has the same formulas in all
its rows.

the reason this is so confusing is because the only way i can keep these
jobs together when i sort is to use helper columns. the helper columns copy
the job name into all the rows for that job, that way they stay together
during sorting.

would it be easier for the code to:

1) go through and distinguish between jobs (based on the change in the
helper columns) then add a double line to break

2) should i just use a space to break up the different jobs, and copy the
formulas into the blank row (to keep the blank row as part of the job)

"steve" wrote:

that was awesome man, thanks a lot

"Dave Peterson" wrote:

Try doing this by hand once. You'll may see that all the rows are grouped
together--since there isn't a break between each group.

But this may work for you:

Option Explicit
Sub testme01()

Dim TopCell As Range
Dim BotCell As Range
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
'clean up first.
.Cells.ClearOutline

FirstRow = 2
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row

Set TopCell = .Cells(FirstRow, "A")
Set BotCell = TopCell

For iRow = FirstRow + 1 To LastRow
If .Cells(iRow, "A").Borders(xlEdgeBottom).LineStyle _
= xlDouble Then
Set BotCell = .Cells(iRow, "A")
.Range(TopCell.Offset(1, 0), BotCell).Rows.Group
Set TopCell = BotCell.Offset(1, 0)
Set BotCell = TopCell
End If
Next iRow

If BotCell.Row = LastRow Then
'do nothing
Else
.Range(BotCell.Offset(1, 0), .Cells(LastRow, "a")).Rows.Group
End If

End With

End Sub

steve wrote:

is there a code to go through by rows and find when a certain cell border
comes up? For example, if i have rows lumped in groups of three, and each
set of three is seperated by the double line border.

is there a way to tell it to group (Datat--Group) these three together
based on the fact that they are withing this border?

for example, (i would not know how to program this)

search row by row
when it finds the double line, start grouping.
when it finds the double line again, stop that group and start another one

thanks
steve

--

Dave Peterson

Double lines are just formatting, right?

First thing I would try to do is to put all my data on one row. It makes
sorting much easier.

If I couldn't do that, then I'd use the helper columns (like you did).

But then I'd clear the formatting and reapply it--just add that code to your
sort macro.

I don't like putting extra blank rows in my data.

===
One more option to make reading a little easier. Instead of using double lines,
just double the height of the first row of each group (with alignment at the
bottom). It'll give the look that the group is double spaced.

(you'll have to autofit your rows, sort, then reapply the row height doubling,
too.)

steve wrote:

going off the code you gave me.....

the grouping part worked perfect. i also have sort buttons with the code
you gave me worked into them. the issue is that the double lines do not
move. i'm working on a way that it places the double line in the rows where
the formulas change. this is because each job has the same formulas in all
its rows.

the reason this is so confusing is because the only way i can keep these
jobs together when i sort is to use helper columns. the helper columns copy
the job name into all the rows for that job, that way they stay together
during sorting.

would it be easier for the code to:

1) go through and distinguish between jobs (based on the change in the
helper columns) then add a double line to break

2) should i just use a space to break up the different jobs, and copy the
formulas into the blank row (to keep the blank row as part of the job)

"steve" wrote:

that was awesome man, thanks a lot

"Dave Peterson" wrote:

Try doing this by hand once. You'll may see that all the rows are grouped
together--since there isn't a break between each group.

But this may work for you:

Option Explicit
Sub testme01()

Dim TopCell As Range
Dim BotCell As Range
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
'clean up first.
.Cells.ClearOutline

FirstRow = 2
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row

Set TopCell = .Cells(FirstRow, "A")
Set BotCell = TopCell

For iRow = FirstRow + 1 To LastRow
If .Cells(iRow, "A").Borders(xlEdgeBottom).LineStyle _
= xlDouble Then
Set BotCell = .Cells(iRow, "A")
.Range(TopCell.Offset(1, 0), BotCell).Rows.Group
Set TopCell = BotCell.Offset(1, 0)
Set BotCell = TopCell
End If
Next iRow

If BotCell.Row = LastRow Then
'do nothing
Else
.Range(BotCell.Offset(1, 0), .Cells(LastRow, "a")).Rows.Group
End If

End With

End Sub

steve wrote:

is there a code to go through by rows and find when a certain cell border
comes up? For example, if i have rows lumped in groups of three, and each
set of three is seperated by the double line border.

is there a way to tell it to group (Datat--Group) these three together
based on the fact that they are withing this border?

for example, (i would not know how to program this)

search row by row
when it finds the double line, start grouping.
when it finds the double line again, stop that group and start another one

thanks
steve

--

Dave Peterson


--

Dave Peterson

ok, so working off of the program you gave me befo

let's say i want to stick with the double lines. the code should first
clear the formatting, sort the data, then reapply the double lines, then use
the code you gave me before.

the part i'm stuck on is reapplying the double lines. how do i tell it to
put a double line where it is a new job? along the helper column, each time
the name changes it is a new job. i know i have to do something with this,
but i'm lost on how to apply it. thanks again for all your help.

steve



"Dave Peterson" wrote:

Double lines are just formatting, right?

First thing I would try to do is to put all my data on one row. It makes
sorting much easier.

If I couldn't do that, then I'd use the helper columns (like you did).

But then I'd clear the formatting and reapply it--just add that code to your
sort macro.

I don't like putting extra blank rows in my data.

===
One more option to make reading a little easier. Instead of using double lines,
just double the height of the first row of each group (with alignment at the
bottom). It'll give the look that the group is double spaced.

(you'll have to autofit your rows, sort, then reapply the row height doubling,
too.)

steve wrote:

going off the code you gave me.....

the grouping part worked perfect. i also have sort buttons with the code
you gave me worked into them. the issue is that the double lines do not
move. i'm working on a way that it places the double line in the rows where
the formulas change. this is because each job has the same formulas in all
its rows.

the reason this is so confusing is because the only way i can keep these
jobs together when i sort is to use helper columns. the helper columns copy
the job name into all the rows for that job, that way they stay together
during sorting.

would it be easier for the code to:

1) go through and distinguish between jobs (based on the change in the
helper columns) then add a double line to break

2) should i just use a space to break up the different jobs, and copy the
formulas into the blank row (to keep the blank row as part of the job)

"steve" wrote:

that was awesome man, thanks a lot

"Dave Peterson" wrote:

Try doing this by hand once. You'll may see that all the rows are grouped
together--since there isn't a break between each group.

But this may work for you:

Option Explicit
Sub testme01()

Dim TopCell As Range
Dim BotCell As Range
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
'clean up first.
.Cells.ClearOutline

FirstRow = 2
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row

Set TopCell = .Cells(FirstRow, "A")
Set BotCell = TopCell

For iRow = FirstRow + 1 To LastRow
If .Cells(iRow, "A").Borders(xlEdgeBottom).LineStyle _
= xlDouble Then
Set BotCell = .Cells(iRow, "A")
.Range(TopCell.Offset(1, 0), BotCell).Rows.Group
Set TopCell = BotCell.Offset(1, 0)
Set BotCell = TopCell
End If
Next iRow

If BotCell.Row = LastRow Then
'do nothing
Else
.Range(BotCell.Offset(1, 0), .Cells(LastRow, "a")).Rows.Group
End If

End With

End Sub

steve wrote:

is there a code to go through by rows and find when a certain cell border
comes up? For example, if i have rows lumped in groups of three, and each
set of three is seperated by the double line border.

is there a way to tell it to group (Datat--Group) these three together
based on the fact that they are withing this border?

for example, (i would not know how to program this)

search row by row
when it finds the double line, start grouping.
when it finds the double line again, stop that group and start another one

thanks
steve

--

Dave Peterson


--

Dave Peterson