Hi Board!
I am trying to help some users to sort data correctly by attaching the sort function to a button. If they do not add any columns to the sheet, they can auto-sort (via the button) which has this code attached:
Selection.Sort Key1:=Range("AC9"), Order1:=xlDescending, Header:=xlYes, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
The "Header" in the above refers to text in cell "AC8" which says "Total."
My problem is that the users may insert or delete columns prior to sorting, and so the Header cell ("Total") may not be "AC8" but instead be "AF8" or "H8". In any case, it will be the last active column of the worksheet, and SHOULD always be in row 8, with data beginning in row 9, but could have any number of rows below that.
How can I automate this sort when I don't have any real "constants"?
While I am here, if I could also delete any rows where "Total" = 0 (again, keeping in mind that the Total column could be anywhere, but should be in row 8, and the number of data rows SHOULD start at 9 but is totally variable as to number of rows below that), it would be WONDERFUL!!
ANY of this do-able??
I am trying to help some users to sort data correctly by attaching the sort function to a button. If they do not add any columns to the sheet, they can auto-sort (via the button) which has this code attached:
Selection.Sort Key1:=Range("AC9"), Order1:=xlDescending, Header:=xlYes, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
The "Header" in the above refers to text in cell "AC8" which says "Total."
My problem is that the users may insert or delete columns prior to sorting, and so the Header cell ("Total") may not be "AC8" but instead be "AF8" or "H8". In any case, it will be the last active column of the worksheet, and SHOULD always be in row 8, with data beginning in row 9, but could have any number of rows below that.
How can I automate this sort when I don't have any real "constants"?
While I am here, if I could also delete any rows where "Total" = 0 (again, keeping in mind that the Total column could be anywhere, but should be in row 8, and the number of data rows SHOULD start at 9 but is totally variable as to number of rows below that), it would be WONDERFUL!!
ANY of this do-able??