Click-Sort Button: Dealing With Now-Irrelevant Subtotals?

[PeteCresswell]

I've been putting little buttons in the column headers of some of the
spreadsheets I create so the user can click the column header and have
the sheet sorted by values in that column.

Works pretty well.

But now the user wants that functionality on a sheet that may have
subtotals.

It works but, of course, the subtotals get blindly sorted depending on
the subtotal row headers as well as the "real" data in the same
columns - and now the user has a sheet that shows rows labeled as
subtotals, but whose amounts reflect some other range than what's
above them. It also pops "Circular Reference" errors periodically.

I guess I could somehow identify the subtotal lines and delete them
the first time the sort routine is invoked... but that would make the
subtotals sort of go "Poof!" before the user's eyes - with no
explaination.

Or maybe I could grey out the subtotal rows and change Font.Style to
something like StrikeThrough as part of the sort routine.

Anybody figured out a way to deal with this gracefully?

 

[(PeteCresswell)]

Per PeteCresswell:

Anybody figured out a way to deal with this gracefully?

I wound up just deleting those rows - passing a couple of
locator/identifier parms to the VB code that created the routine
in the .XLS.

Only tricky part was preserving any border around the data area
if there happened tb a subtotal line at the bottom of said area.

If anybody cares, there's a downloadable sample of a .XLS at
www.wchs59.com/ClickSortSample.xls

Note that the center sheet doesn't have a border at the bottom to
begin with... it's not the click/sort routine that's losing it.