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

When you start the helper cells are ok?

If yes, you could add the formulas in your code, convert those formulas to
values, then sort. Then starting at the bottom, work your way up that helper
column looking for differences.



steve wrote:

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


--

Dave Peterson

would you mind giving me a quick example on how to see that the data is
changing. i know i'm a pain, but i'm having trouble writing this thing
thanks

"Dave Peterson" wrote:

When you start the helper cells are ok?

If yes, you could add the formulas in your code, convert those formulas to
values, then sort. Then starting at the bottom, work your way up that helper
column looking for differences.



steve wrote:

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


--

Dave Peterson

I don't understand your question.

But if you know your formula and the range it goes, maybe something like this
will be enough.

dim LastRow as long
dim myFormula as string
myformula = "=whateveryouusedintheworksheet"
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
with .range("g2:g"&lastrow)
.formula = myformula
.value = .value
end with
'do the sort
'remove the formatting
'reapply the formatting
end with

(Am I close???)


steve wrote:

would you mind giving me a quick example on how to see that the data is
changing. i know i'm a pain, but i'm having trouble writing this thing
thanks

"Dave Peterson" wrote:

When you start the helper cells are ok?

If yes, you could add the formulas in your code, convert those formulas to
values, then sort. Then starting at the bottom, work your way up that helper
column looking for differences.



steve wrote:

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


--

Dave Peterson


--

Dave Peterson

rather than use the formulas, we can just use the values in the cells. after
these values change, i want to add a double line. for example, if the job
name was Wawa:

Wawa
Wawa
Wawa

that would appear in the helper column. so it should go through that, and
when the value changes from let's say Wawa to Walgreens, it adds a double
line between them. so my question is, how do i go through the helper column
and add double lines when the value changes? also, they need to be cleared
and reapplied each time the sort button is hit.

thanks a lot for your help man, i appreciate it
steve

"Dave Peterson" wrote:

I don't understand your question.

But if you know your formula and the range it goes, maybe something like this
will be enough.

dim LastRow as long
dim myFormula as string
myformula = "=whateveryouusedintheworksheet"
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
with .range("g2:g"&lastrow)
.formula = myformula
.value = .value
end with
'do the sort
'remove the formatting
'reapply the formatting
end with

(Am I close???)


steve wrote:

would you mind giving me a quick example on how to see that the data is
changing. i know i'm a pain, but i'm having trouble writing this thing
thanks

"Dave Peterson" wrote:

When you start the helper cells are ok?

If yes, you could add the formulas in your code, convert those formulas to
values, then sort. Then starting at the bottom, work your way up that helper
column looking for differences.



steve wrote:

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


--

Dave Peterson


--

Dave Peterson

One way:

Option Explicit
Sub testme()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet
Dim HelperColumn As Long
Dim ColsToUnderline As Long

Set wks = Worksheets("Sheet1")
ColsToUnderline = 12

With wks
HelperColumn = .Range("a1").Column
With .Cells
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
End With

FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, HelperColumn).End(xlUp).Row

For iRow = LastRow + 1 To FirstRow + 1 Step -1
If .Cells(iRow, HelperColumn).Value _
= .Cells(iRow - 1, HelperColumn).Value Then
'do nothing, same value
Else
With .Cells(iRow, 1).Resize(1, ColsToUnderline) _
.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End If
Next iRow
End With

End Sub

I worked from the bottom up. But it isn't necessary for this kind of
formatting. But if you ever decide to use the same technique to remove rows,
starting from the bottom makes life much easier.

steve wrote:

rather than use the formulas, we can just use the values in the cells. after
these values change, i want to add a double line. for example, if the job
name was Wawa:

Wawa
Wawa
Wawa

that would appear in the helper column. so it should go through that, and
when the value changes from let's say Wawa to Walgreens, it adds a double
line between them. so my question is, how do i go through the helper column
and add double lines when the value changes? also, they need to be cleared
and reapplied each time the sort button is hit.

thanks a lot for your help man, i appreciate it
steve

"Dave Peterson" wrote:

I don't understand your question.

But if you know your formula and the range it goes, maybe something like this
will be enough.

dim LastRow as long
dim myFormula as string
myformula = "=whateveryouusedintheworksheet"
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
with .range("g2:g"&lastrow)
.formula = myformula
.value = .value
end with
'do the sort
'remove the formatting
'reapply the formatting
end with

(Am I close???)


steve wrote:

would you mind giving me a quick example on how to see that the data is
changing. i know i'm a pain, but i'm having trouble writing this thing
thanks

"Dave Peterson" wrote:

When you start the helper cells are ok?

If yes, you could add the formulas in your code, convert those formulas to
values, then sort. Then starting at the bottom, work your way up that helper
column looking for differences.



steve wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

you're sick...haha
thanks man, that worked nice.

steve

is it cool if i keep this string going if i have questions later? or should
i start a new one??

"Dave Peterson" wrote:

One way:

Option Explicit
Sub testme()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet
Dim HelperColumn As Long
Dim ColsToUnderline As Long

Set wks = Worksheets("Sheet1")
ColsToUnderline = 12

With wks
HelperColumn = .Range("a1").Column
With .Cells
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
End With

FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, HelperColumn).End(xlUp).Row

For iRow = LastRow + 1 To FirstRow + 1 Step -1
If .Cells(iRow, HelperColumn).Value _
= .Cells(iRow - 1, HelperColumn).Value Then
'do nothing, same value
Else
With .Cells(iRow, 1).Resize(1, ColsToUnderline) _
.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End If
Next iRow
End With

End Sub

I worked from the bottom up. But it isn't necessary for this kind of
formatting. But if you ever decide to use the same technique to remove rows,
starting from the bottom makes life much easier.

steve wrote:

rather than use the formulas, we can just use the values in the cells. after
these values change, i want to add a double line. for example, if the job
name was Wawa:

Wawa
Wawa
Wawa

that would appear in the helper column. so it should go through that, and
when the value changes from let's say Wawa to Walgreens, it adds a double
line between them. so my question is, how do i go through the helper column
and add double lines when the value changes? also, they need to be cleared
and reapplied each time the sort button is hit.

thanks a lot for your help man, i appreciate it
steve

"Dave Peterson" wrote:

I don't understand your question.

But if you know your formula and the range it goes, maybe something like this
will be enough.

dim LastRow as long
dim myFormula as string
myformula = "=whateveryouusedintheworksheet"
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
with .range("g2:g"&lastrow)
.formula = myformula
.value = .value
end with
'do the sort
'remove the formatting
'reapply the formatting
end with

(Am I close???)


steve wrote:

would you mind giving me a quick example on how to see that the data is
changing. i know i'm a pain, but i'm having trouble writing this thing
thanks

"Dave Peterson" wrote:

When you start the helper cells are ok?

If yes, you could add the formulas in your code, convert those formulas to
values, then sort. Then starting at the bottom, work your way up that helper
column looking for differences.



steve wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

If it's a new subject, start a new thread.

If it's a longgggggggg time between posts, start a new thread, but give enough
info to explain what you've tried.

If it's a short time and it's about this subject, stay here.



steve wrote:

you're sick...haha
thanks man, that worked nice.

steve

is it cool if i keep this string going if i have questions later? or should
i start a new one??

"Dave Peterson" wrote:

One way:

Option Explicit
Sub testme()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet
Dim HelperColumn As Long
Dim ColsToUnderline As Long

Set wks = Worksheets("Sheet1")
ColsToUnderline = 12

With wks
HelperColumn = .Range("a1").Column
With .Cells
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
End With

FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, HelperColumn).End(xlUp).Row

For iRow = LastRow + 1 To FirstRow + 1 Step -1
If .Cells(iRow, HelperColumn).Value _
= .Cells(iRow - 1, HelperColumn).Value Then
'do nothing, same value
Else
With .Cells(iRow, 1).Resize(1, ColsToUnderline) _
.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End If
Next iRow
End With

End Sub

I worked from the bottom up. But it isn't necessary for this kind of
formatting. But if you ever decide to use the same technique to remove rows,
starting from the bottom makes life much easier.

steve wrote:

rather than use the formulas, we can just use the values in the cells. after
these values change, i want to add a double line. for example, if the job
name was Wawa:

Wawa
Wawa
Wawa

that would appear in the helper column. so it should go through that, and
when the value changes from let's say Wawa to Walgreens, it adds a double
line between them. so my question is, how do i go through the helper column
and add double lines when the value changes? also, they need to be cleared
and reapplied each time the sort button is hit.

thanks a lot for your help man, i appreciate it
steve

"Dave Peterson" wrote:

I don't understand your question.

But if you know your formula and the range it goes, maybe something like this
will be enough.

dim LastRow as long
dim myFormula as string
myformula = "=whateveryouusedintheworksheet"
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
with .range("g2:g"&lastrow)
.formula = myformula
.value = .value
end with
'do the sort
'remove the formatting
'reapply the formatting
end with

(Am I close???)


steve wrote:

would you mind giving me a quick example on how to see that the data is
changing. i know i'm a pain, but i'm having trouble writing this thing
thanks

"Dave Peterson" wrote:

When you start the helper cells are ok?

If yes, you could add the formulas in your code, convert those formulas to
values, then sort. Then starting at the bottom, work your way up that helper
column looking for differences.



steve wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

how come when you sort, all of the formatting follows where the data goes,
except for the borders? the borders do not move? why is this?

"Dave Peterson" wrote:

If it's a new subject, start a new thread.

If it's a longgggggggg time between posts, start a new thread, but give enough
info to explain what you've tried.

If it's a short time and it's about this subject, stay here.



steve wrote:

you're sick...haha
thanks man, that worked nice.

steve

is it cool if i keep this string going if i have questions later? or should
i start a new one??

"Dave Peterson" wrote:

One way:

Option Explicit
Sub testme()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet
Dim HelperColumn As Long
Dim ColsToUnderline As Long

Set wks = Worksheets("Sheet1")
ColsToUnderline = 12

With wks
HelperColumn = .Range("a1").Column
With .Cells
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
End With

FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, HelperColumn).End(xlUp).Row

For iRow = LastRow + 1 To FirstRow + 1 Step -1
If .Cells(iRow, HelperColumn).Value _
= .Cells(iRow - 1, HelperColumn).Value Then
'do nothing, same value
Else
With .Cells(iRow, 1).Resize(1, ColsToUnderline) _
.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End If
Next iRow
End With

End Sub

I worked from the bottom up. But it isn't necessary for this kind of
formatting. But if you ever decide to use the same technique to remove rows,
starting from the bottom makes life much easier.

steve wrote:

rather than use the formulas, we can just use the values in the cells. after
these values change, i want to add a double line. for example, if the job
name was Wawa:

Wawa
Wawa
Wawa

that would appear in the helper column. so it should go through that, and
when the value changes from let's say Wawa to Walgreens, it adds a double
line between them. so my question is, how do i go through the helper column
and add double lines when the value changes? also, they need to be cleared
and reapplied each time the sort button is hit.

thanks a lot for your help man, i appreciate it
steve

"Dave Peterson" wrote:

I don't understand your question.

But if you know your formula and the range it goes, maybe something like this
will be enough.

dim LastRow as long
dim myFormula as string
myformula = "=whateveryouusedintheworksheet"
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
with .range("g2:g"&lastrow)
.formula = myformula
.value = .value
end with
'do the sort
'remove the formatting
'reapply the formatting
end with

(Am I close???)


steve wrote:

would you mind giving me a quick example on how to see that the data is
changing. i know i'm a pain, but i'm having trouble writing this thing
thanks

"Dave Peterson" wrote:

When you start the helper cells are ok?

If yes, you could add the formulas in your code, convert those formulas to
values, then sort. Then starting at the bottom, work your way up that helper
column looking for differences.



steve wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson