Selection.Sort problem with formulas

[Michael454.2000]

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

 

[Joel]

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

 

[Michael454.2000]

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

 

[Joel]

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)

 

[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]

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]

Uhmmm.... no, that won't work Joel. It's the act of sorting that is the
underlying problem. The physically moving of the sections around causes the
Range Name definitions to get out of wack and no longer match the actual
location of the data they originally pointed to.

The only way I can find to re-arrange the sections of rows (without it
screwing up the summary at the bottom) is to manually cut and paste them in
their entirety... cut all green rows as a chunk and paste them somewhere
else, then cut all blue rows as a chunk and paste them somewhere else, then
cut the blue rows and paste them where I want them to be (order wise), then
cut the green rows and paste them where I want them to be (order wise).

Any other way of sorting them, which breaks up a section during the sort,
throws the Range Name definitions all out of wack. Excel cannot keep track
of the range boundries. Using the Sort method, Excel doesn't even try to
update the Range Name definitions from what I'm seeing.

I'm still at a loss here. Plus I'm giving a simplified example, there are
actually up to 4 possible colored sections of data on a worksheet, and each
section can be from 1 row to 99 rows long. The colors are contiguous and
adjoin each other (ie they differ and have no extra space between them). The
data being summarized is actually more complex as well. I made up a test
spreadsheet to prototype the problem, using only 2 colored sections and very
simple data. Excel's behavior is consistent from my real project to the test
project.

I think what I need is a way to pick the lowest numbered section (by it's
hidden Sort column ranking) and copy it to the clipboard then paste it at
the top of the worksheet (pushing everybody else down), then pick the next
lowest numbered section and copy it to the clipboard then paste it under the
lowest ranking section, etc. etc. until all possible sections have been
sorted... but isn't that going to make alot of blank rows at the bottom?
There are things below these sections which must remain at their exact row
#'s and cannot be pushed down.

Gosh but this sort of thing (pun intended) is soooo much easier to deal with
in Access! But then again, I've over a decade of experience developing in
Access and only a few months with Excel here. It just seems so difficult to
do simple things in Excel sometimes. This should be a simple thing!

- Michael

 

[Michael]

Yes! What I really need is a way to Cut a range (of rows) from the
Worksheet, moving the rows below the cut region up to close the gap, then
Paste those rows to the head of the Worksheet, pushing down the existing
rows (rather than overwriting them, as it does when I manually cut and
paste)!

This would preserve the Range Name definitions, from what I can tell, and
re-order the sections without screwing up stuff below the colored sections.
Any thoughts?

I believe I can code the routine to recursively go thru the entire range of
cells looking for the lowest sort number (by hundreds) and selecting up to
the next colored section ok.... now if I can just cut the selected section
out (pulling what's under it up, to close the gap). How to do that?

- Michael

 

[Joel]

Does this code help?

Sub MoveSections()
'
' Macro1 Macro
' Macro recorded 6/19/2008 by Joel
'

'
Const RED = 3
Const Blue = 41
Const Green = 4
Const Yellow = 6

ColorOrder = Array(Yellow, Green, Blue, RED)


FirstRow = 1
For Each SectColor In ColorOrder
RowCount = 1
Foundcolor = False
Do While Range("A" & RowCount).Interior.ColorIndex <> xlNone
Select Case Foundcolor
Case False
CellColor = _
Range("A" & RowCount).Interior.ColorIndex
If SectColor = CellColor Then
FirstColor = RowCount
Foundcolor = True
End If
Case True
NextColor = _
Range("A" & (RowCount + 1)).Interior.ColorIndex
If SectColor <> NextColor Then
LastColor = RowCount
Exit Do
End If
End Select
RowCount = RowCount + 1
Loop
'test if section is in correct location don't move
If FirstColor <> FirstRow Then
Rows(FirstColor & ":" & LastColor).Cut
Rows(FirstRow).Insert
End If
FirstRow = FirstRow + (LastColor - FirstColor + 1)
Next SectColor
End Sub

 

[Michael]

Joel you've been very helpful from the beginning and I offer you many many
thanks for sticking it out with me on this. Here is the routine I finally
worked up today that solves my problem:

Sub Button2_Click()

Dim StartCell As String

StartCell$ = "C14"
While StartCell$ <> ""
StartCell$ = FindnMoveLowest(StartCell$)
Wend

End Sub

Public Function FindnMoveLowest(StartCell As String) As String

Dim MyCell, MyNext As Range
Dim Again As Boolean
Dim SortPriority As Integer
Dim StartRow As Integer
Dim StopRow As Integer

Set MyCell = ActiveSheet.Range(StartCell$)
TheTop% = MyCell.Row
Again = True
Do While Again
If Not IsEmpty(MyCell) Then
' Found a Priority Sort number, the head of a section
If SortPriority% > 0 Then
' Other sections have been scanned thru
If MyCell.Value < SortPriority% Then
' This section has a lower Sort Priority number than the
previous lowest section
' so replace the previous section's info
SortPriority% = MyCell.Value
StartRow% = MyCell.Row
StopRow% = -1
Else
' We've found a new section but it's Sort Priority isn't
lower then the current lowest
' Record the end row of the current lowest section and
continue looking
StopRow% = MyCell.Row - 1
End If
Else
' This is the 1st section the code has scanned thru
SortPriority% = MyCell.Value
StartRow% = MyCell.Row
StopRow% = -1
End If
End If
On Error Resume Next
Set MyNext = MyCell.Offset(1, 0)
If Err > 0 Or MyNext.Locked = True Then
Again = False
Err = 0
Else
Set MyCell = MyNext
End If
On Error GoTo 0
Loop
If StopRow% = -1 Then
StopRow% = MyCell.Row
End If

If StartRow% > TheTop% Then
ActiveSheet.Unprotect
Rows(StartRow% & ":" & StopRow%).Cut
Rows(TheTop%).Insert
ActiveSheet.Protect
FindnMoveLowest = Left$(StartCell$, 1) & (Val(Mid$(StartCell$, 2)) +
StopRow% - StartRow% + 1)
Else
FindnMoveLowest = ""
End If

End Function

I'm recursively calling a function which looks for the lowest numbered row
(using StartCell$ as the starting point), records the beginning row and
ending row of the section, and then moves that section to the row where it
started it's search at. When it finally cannot find a section that it can
move, the routine stops.

I post this here in hopes that it may help somebody else that bumps into
this problem of the Excel Sort method causing formulas to get screwed up,
and so you can see what I finally did. However I don't think I could have
figured this out without your suggestions Joel as they made me think thru
the problem looking out-of-the-box for a solution.

I think what we both came up with finally is very similar.

Many thanks!

- Michael