I've been Googling all day on this one with no luck.

Excel 2003 spreadsheet with groups (sections) of rows formatted to
have different background colors to differentiate them visually which
are summed up in a summary section at the bottom of the sheet (Sum and
Percent Filled In formulas for each colored section). The sections do
NOT contain the same number of rows each, they vary!

Furthermore, these sections have different sort orders per section and
can be changed on the fly by the user by clicking buttons to change
the order of the colored sections (rows within a section remain the
same, the entire sections get swapped around though)... hidden columns
are used to contain the different sort orders possible for each
section.

The Selection.Sort method is used to re-order them.

When Selection.Sort is applied on the approriate column, it correctly
changes the order of the colored sections... but the formulas are no
longer correct as they aren't being updated automatically and are
still referencing the original cell locations which no longer match
the colored sections!

I've tried defining Range Names and using them in the summary formulas
to no avail... the range definitions do not update when Selection.Sort
is applied either apparently.

So how can I fix this problem? I have to have the different sections
and the sections have to be contiguous and different sizes (row wise)
with a sheet summary at the bottom. Is there a simple solution I'm
missing? Or do I have to write VBA code to programatically update all
the summary formulas to point to the new section locations (not fun,
IMHO)?

I notice that if I manually cut and paste the sections to different
locations the Range Name definitions do get automatically updated, no
matter where on the sheet I paste each section. Why does
Selection.Sort not update the Range Name definitions automatically?

Thanks for any help ya'll can provide! I'm at wits end here.

ps. I've a BS in Computer Science and have been doing Visual Basic,
Access and SQL Server develoment for some 15 years now... I've only
recently started seriously working in Excel though so the objects I'm
dealing with are a little new to me (ranges and worksheets and cells
instead of fields and tables and forms and the like).

- Michael

Try a bublesort using a cut and paste like the one below.


Sub BubleSort(target As Range, SortCol As String)

FirstRow = target.Row
LastRow = target.SpecialCells(xlCellTypeLastCell).Row

RowCount1 = FirstRow
Do While RowCount1 <= LastRow - 1
RowCount2 = RowCount1 + 1
Do While RowCount2 <= LastRow
If Range(SortCol & RowCount1) _
Range(SortCol & RowCount2) Then

Rows(RowCount2).Cut Destination:=Rows(RowCount1)

End If
RowCount2 = RowCount2 + 1
Loop
RowCount1 = RowCount1 + 1
Loop
End Sub


" wrote:

I've been Googling all day on this one with no luck.

Excel 2003 spreadsheet with groups (sections) of rows formatted to
have different background colors to differentiate them visually which
are summed up in a summary section at the bottom of the sheet (Sum and
Percent Filled In formulas for each colored section). The sections do
NOT contain the same number of rows each, they vary!

Furthermore, these sections have different sort orders per section and
can be changed on the fly by the user by clicking buttons to change
the order of the colored sections (rows within a section remain the
same, the entire sections get swapped around though)... hidden columns
are used to contain the different sort orders possible for each
section.

The Selection.Sort method is used to re-order them.

When Selection.Sort is applied on the approriate column, it correctly
changes the order of the colored sections... but the formulas are no
longer correct as they aren't being updated automatically and are
still referencing the original cell locations which no longer match
the colored sections!

I've tried defining Range Names and using them in the summary formulas
to no avail... the range definitions do not update when Selection.Sort
is applied either apparently.

So how can I fix this problem? I have to have the different sections
and the sections have to be contiguous and different sizes (row wise)
with a sheet summary at the bottom. Is there a simple solution I'm
missing? Or do I have to write VBA code to programatically update all
the summary formulas to point to the new section locations (not fun,
IMHO)?

I notice that if I manually cut and paste the sections to different
locations the Range Name definitions do get automatically updated, no
matter where on the sheet I paste each section. Why does
Selection.Sort not update the Range Name definitions automatically?

Thanks for any help ya'll can provide! I'm at wits end here.

ps. I've a BS in Computer Science and have been doing Visual Basic,
Access and SQL Server develoment for some 15 years now... I've only
recently started seriously working in Excel though so the objects I'm
dealing with are a little new to me (ranges and worksheets and cells
instead of fields and tables and forms and the like).

- Michael

Thanks Joel, but your code doesn't seem to work... all the data
disappears from the sheet when I executed your Bublesort!

I then closed Excel (without saving!) and get errors about a large
picture stored on the clipboard.... I have no idea what that's about.

It looks like your sub cuts the rows to the clipboard, but where does
it paste anything back? Did I miss something?

Thanks for trying... I'm still looking for a solution to this problem
however. 8-(

- Michael

It was just menat as an example. This change will work.

from
Rows(RowCount2).Cut Destination:=Rows(RowCount1)
to
Rows(RowCount2).Cut
Rows(RowCount1).Insert (xlShiftDown)


" wrote:

Thanks Joel, but your code doesn't seem to work... all the data
disappears from the sheet when I executed your Bublesort!

I then closed Excel (without saving!) and get errors about a large
picture stored on the clipboard.... I have no idea what that's about.

It looks like your sub cuts the rows to the clipboard, but where does
it paste anything back? Did I miss something?

Thanks for trying... I'm still looking for a solution to this problem
however. 8-(

- Michael

Well that is better but still no joy... for some reason, I now get a bunch
of blank rows at the top of the cell range after applying this sort. But
even more importantly, the BubbleSort wreaks havoc with the Range Names I
have defined (resulting in the formulas in the summary section all being
wrong afterwards).

I'm guessing I'm going to have to use a technique of some kind to cut and
paste the entire colored section instead of row-by-row... .of course, since
sections aren't the same size (number of rows) necessarily that's going to
make things even more complicated. (sigh)

The worksheet(s) I'm doing resembles this:

Row Color Data Sort
1 Blue 2 100
2 Blue 1 101
3 Blue 0 102
4 Green 1 200
5 Green 5 201

Where Blue and Green aren't columns but are the section colors (background
colors of the rows). Blue and Green are also the defined Range Names for the
appropriate rows (Blue = $1$Sort:$3$Sort, Green = $4$Sort:$5$Sort)! Sort is
a hidden column specifically for sorting the sections. The user can sort
ascending or descending by a button.

Now if you have a summary at the bottom of the sheet such as:

Blue = Sum(Blue)
Green = Sum(Green)

Then using the Sort method of Excel to sort the colored sections in
ascending order by the hidden Sort column, the summary formulas (and defined
Range Name cells) don't change to match the new sort order of the data.
Suddenly the Sum(Blue) is actually summing the green cells (now at rows 1
and 2) and 1 blue cell (now at row 3), and Sum(Green) is actually summing
the 2 remaining Blue cells at the bottom (rows 4 and 5). 8-(

Appling this Bubble Sort VBA code instead of the built in Sort method
however, all the data are sorted but I get a bunch of blank rows added in at
the top of the range AND my defined Range Names are all over the place (as
the rows were cut and pasted Excel tried to keep the range name definitions
in sync but failed miserably evidentally).

Since every section's Sort begins in increments of 100 (there are never more
than 100 rows in a section), can I use the 100's to decide sort order and
cut and paste the entire section instead of row-by-row? If so though, I have
another problem in that the sections aren't the same size so I can't simple
replace 1 section with another section as they may not fit (either too few
rows or too many rows!).

Ok I'm gonna take a break and go pull my hair out now. Ughhh! This is a
difficult task to get working I swear.

- Michael

"Joel" wrote in message
...
It was just menat as an example. This change will work.

from
Rows(RowCount2).Cut Destination:=Rows(RowCount1)
to
Rows(RowCount2).Cut
Rows(RowCount1).Insert (xlShiftDown)

I have simple solution. Create a udf to return the color index.

function colorIndex(target as Range)

colorIndex = Target.interior.colorindex)

end function

Add a new column with the formula
=colorindex(A1) and copy down column

Now you have to change the formula to a value by using copy then
pastespecial Value to remove the formula. The formula won't work after the
sort. Now you can sort on the color by using the new column. You can also
sum using the new column in a countif() function.

"Michael" wrote:

Well that is better but still no joy... for some reason, I now get a bunch
of blank rows at the top of the cell range after applying this sort. But
even more importantly, the BubbleSort wreaks havoc with the Range Names I
have defined (resulting in the formulas in the summary section all being
wrong afterwards).

I'm guessing I'm going to have to use a technique of some kind to cut and
paste the entire colored section instead of row-by-row... .of course, since
sections aren't the same size (number of rows) necessarily that's going to
make things even more complicated. (sigh)

The worksheet(s) I'm doing resembles this:

Row Color Data Sort
1 Blue 2 100
2 Blue 1 101
3 Blue 0 102
4 Green 1 200
5 Green 5 201

Where Blue and Green aren't columns but are the section colors (background
colors of the rows). Blue and Green are also the defined Range Names for the
appropriate rows (Blue = $1$Sort:$3$Sort, Green = $4$Sort:$5$Sort)! Sort is
a hidden column specifically for sorting the sections. The user can sort
ascending or descending by a button.

Now if you have a summary at the bottom of the sheet such as:

Blue = Sum(Blue)
Green = Sum(Green)

Then using the Sort method of Excel to sort the colored sections in
ascending order by the hidden Sort column, the summary formulas (and defined
Range Name cells) don't change to match the new sort order of the data.
Suddenly the Sum(Blue) is actually summing the green cells (now at rows 1
and 2) and 1 blue cell (now at row 3), and Sum(Green) is actually summing
the 2 remaining Blue cells at the bottom (rows 4 and 5). 8-(

Appling this Bubble Sort VBA code instead of the built in Sort method
however, all the data are sorted but I get a bunch of blank rows added in at
the top of the range AND my defined Range Names are all over the place (as
the rows were cut and pasted Excel tried to keep the range name definitions
in sync but failed miserably evidentally).

Since every section's Sort begins in increments of 100 (there are never more
than 100 rows in a section), can I use the 100's to decide sort order and
cut and paste the entire section instead of row-by-row? If so though, I have
another problem in that the sections aren't the same size so I can't simple
replace 1 section with another section as they may not fit (either too few
rows or too many rows!).

Ok I'm gonna take a break and go pull my hair out now. Ughhh! This is a
difficult task to get working I swear.

- Michael

"Joel" wrote in message
...
It was just menat as an example. This change will work.

from
Rows(RowCount2).Cut Destination:=Rows(RowCount1)
to
Rows(RowCount2).Cut
Rows(RowCount1).Insert (xlShiftDown)