Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Creating
Hello All,
I have a worksheet of data with the following characteristics: --column headers --numeric and textual data --a "frozen" pane --some columns in which input is constrained by validation lists stored on other worksheets within the same Excel file What I need to do is make it so that each of the main worksheet's column headers present my Excel-newbie end-users with a simple dropdown/pulldown that allows them to sort (ascending and descending) all the rows across all of the columns, i.e. across the frozen pane line, across the validation-list columns, etc. In an ideal world, users would be able to sort by multiple columns, i.e. by A then B then C, but I'll happily settle for sorting by one column. For the life of me, I can't seem to figure this out. Thanks in advance for any help -- it's much appreciated. Jack |
#2
|
|||
|
|||
Creating
How about this. You put some rectangles over each of the header cells in row
1. Make them invisible. When the user clicks on one of those rectangles, your data gets sorted by that column (click on the same rectangle, it sorts in the opposite direction). If that sounds promising... Saved from a previous post... I put rectangles over the headers (made the borders invisible) so that when you clicked on the rectangle, it looked like you were clicking on the header. Option Explicit Sub setupOneTime() Dim myRng As Range Dim myCell As Range Dim curWks As Worksheet Dim myRect As Shape Set curWks = ActiveSheet With curWks '10 columns Set myRng = .Range("a1").Resize(1, 10) For Each myCell In myRng.Cells With myCell Set myRect = .Parent.Shapes.AddShape _ (Type:=msoShapeRectangle, _ Top:=.Top, Height:=.Height, _ Width:=.Width, Left:=.Left) End With With myRect .OnAction = ThisWorkbook.Name & "!SortTable" .Fill.Visible = False .Line.Visible = False End With Next myCell End With End Sub Sub sortTable() Dim myTable As Range Dim myColToSort As Long Dim curWks As Worksheet Dim mySortOrder As Long Dim LastRow As Long Set curWks = ActiveSheet With curWks myColToSort = .Shapes(Application.Caller).TopLeftCell.Column LastRow = .Cells(.Rows.Count, "a").End(xlUp).Row Set myTable = .Range("a1:a" & LastRow).Resize(, 10) If .Cells(myTable.Row + 1, myColToSort).Value _ < .Cells(LastRow, myColToSort).Value Then mySortOrder = xlDescending Else mySortOrder = xlAscending End If myTable.Sort key1:=.Cells(myTable.Row, myColToSort), _ order1:=mySortOrder, _ header:=xlYes End With End Sub === The setuponetime routine puts the rectangles in 10 columns A1:J1 with this: '10 columns Set myRng = .Range("a1").Resize(1, 10) The sortTable routine sorts that same table based on the number of cells used in column A (and 10 columns wide) with this line: Set myTable = .Range("a1:a" & LastRow).Resize(, 10) If you have more or less columns, adjust both .resize(,10) portions. If you have to use a different column to get the last row, adjust this line: LastRow = .Cells(.Rows.Count, "a").End(xlUp).Row ActualSelf wrote: Hello All, I have a worksheet of data with the following characteristics: --column headers --numeric and textual data --a "frozen" pane --some columns in which input is constrained by validation lists stored on other worksheets within the same Excel file What I need to do is make it so that each of the main worksheet's column headers present my Excel-newbie end-users with a simple dropdown/pulldown that allows them to sort (ascending and descending) all the rows across all of the columns, i.e. across the frozen pane line, across the validation-list columns, etc. In an ideal world, users would be able to sort by multiple columns, i.e. by A then B then C, but I'll happily settle for sorting by one column. For the life of me, I can't seem to figure this out. Thanks in advance for any help -- it's much appreciated. Jack -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating custom list with a comma in it | Excel Discussion (Misc queries) | |||
Toggle switch between absolute and relative values creating a macr | Excel Discussion (Misc queries) | |||
Can I import a new colour into the options for creating charts? | Charts and Charting in Excel | |||
Eliminate creating list that returns blank cells | Excel Worksheet Functions | |||
I am having problems creating pivot table of data | Charts and Charting in Excel |