Solution needed to sort via button after deleting rows

  • Thread starter Thread starter FISH
  • Start date Start date
F

FISH

Hello all,

I have a spreadsheet where I have 6 different sets of data that need to be
sorted often. This data changes often so I want to do this via a button
users can simply click on to sort.
Someone here helped me add the buttons and assign a macro to do the sorting
and things work fine as long as the spreadsheet stays as is. The problem we
couldn't overcome was things go wrong when I have to add or delete some rows
within the set of data.


For example:

I've assigned a macro to a button to sort R58 down to R77 and things work
fine. But, when I delete Rows 67, 68, 69, and 70 and click the button,
things get all messed up. The macro is still assigned to sort the same set
of data between R58-R77 even though 4 rows have been deleted and in order to
sort the same data it should only now sort R58-R73.


Does anyone know of a way this can work after deleting or adding new rows?
Or if there was a way to assign a function to a specific cell to do the
sorting when clicked on, that would work also.


Thanks in advance for any help.
 
Post the code you have and I will have a look at it for you but no promises.

Also a couple of questions:-

1. Will the first or last row ever be deleted?
2. Are there any headers in first row to be sorted or just data?

Regards,

OssieMac
 
Another question. Is there any data below the bottom of the rows to be sorted?

Regards,

OssieMac
 
Thanks for the offer to help. Here are the answers to your questions:

1.) "Will the first or last row ever be deleted?"

They might be deleted, but if this is a problem, I can work around it and
make sure these rows are never deleted.

2. "Are there any headers in first row to be sorted or just data?"

Column R has all the results from each of the individual rows and these
results in column R are the things to be sorted. But when they move they
need to move with the entire row of data associated with those results.


3.) "Is there any data below the bottom of the rows to be sorted?"

There are 6 different sets of data all lined up below each other, separated
by a few rows here and there. (sets are from R6-R15, R21-R40, R46-R65,
R71-R80, R86-R95, and the last one is from R101-R110). Each of these data
sets need to be sorted separately using different buttons.


Thanks again !!!
 
Hi Fish,

Ok now with the info you have given me it makes it easy. Name each range and
use the named ranges for sorting.

To name a range:-

In Xl2007 version: Select the range and then Formulas ribbon->Define Name

In Pre xl2007 versions: Select the range and then Insert->Name->Define.

For example:-

On the worksheet select rows R6-R15 then define this as say First_Sort. Then
repeat for Second_Sort etc. (Note no spaces in the defined name)

In the macro instead of referencing the sort ranges as:-

Rows("6:15") (or Range("A6:IV15"))

You reference them as :-

Range("First_Sort") ' Note Range not Rows and still use double quotes.

When you insert or delete rows the range increases or decreases by the
number of rows. However, if you delete the entire named range then the named
range indicates an error.

Hope this answers your question.

Regards,

OssieMac
 
Hi again,

One problem I omitted. If you try to insert at the first row then the insert
goes above the named range and is then not included in the named range. For
example if named range First_Sort is R6-R15 and you select R6 then insert,
then First_Sort range will become R7-R16. However, you can select the last
row and insert and the new row is included in the named range.

Reason for this is that rows are inserted above the selection.

Regards,

OssieMac
 
Hi Fish & OssieMac

I am the someone who tried to help Fish, but I could not get away from cell
references to the named ranges, but thought it was possible, didn't know
how.

Fish is looking for "a button click" to delete and then sort and make it
work. So Vb code is the answer as far as I know.

I will review your suggestions and give it a go.

Thanks,
Howard
 
Back
Top