|
Sort Data With Headers in Ascending Order in Excel
Macro that sorts data that has headers in ascending order in Excel. This macro assumes that you data has headers on it. This means that you data has a row that labels what the data underneath it is or stands for in the worksheet. The header row will not be sorted; however everything underneath that row will be sorted. Header rows are assumed to be one single row in Excel. This means that if your header row is actually two rows, you need to account for this by moving the data table range that you will list in the macro down one.
This macro sorts one column within the data set. This is a simple sort macro for Excel and works in most versions of Excel.
To use this Excel macro, simply replace A1:C56 with the range of the entire data table that will be changed or re-arranged by the sort and then change A1 to point to the column or top cell in the column of the data from which you would like to sort the entire data set.
Where to install the macro: Module
Excel Macro to Sort Data With Headers in Ascending Order in Excel
Sub Sort_Ascending_With_Header()
'Sorts a worksheet in ascending order and assumes there are headers on the data
Range("A1:C56").Sort _
Key1:=Range("A1"), Header:=xlYes
End Sub
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
VB:
Holdings_first.Rows("1:1").Select
Holdings_first.Range("D1").Activate
Selection.AutoFilter
Holdings_first.AutoFilter.Sort.SortFields.Clear
Holdings_first.AutoFilter.Sort.SortFields.Add Key:=Range _
("D2:D" & last_row1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With Holdings_first.AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.AutoFilter
If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
This part of my code won't work, I get soo frustrated with using Autofilter, it hurts me to use the recording button, but I am not sure how else to get this right, any suggestions on how to get this code to work, and on how I can get my head wrapped around this autofilter frustration I have
thanks in advance for any help.
Hello, I would like to sort this data to be sorted in ascending order everytime new data is uploaded onto the sheet 1 , changing the average.
The ranking sheet is the one which would contain the ranking in terms of average. (BA column sheet1)
I've been trying to run a macro but it does not seem to work, I am attaching the file , I would GREATLY appreciate your help !
Thank you very much
I need to sort the last six numbers in a column in ascending order? What is the formula to do this. I know I need to insert a column next to the cells I am wanting to sort but I don't know how to word the formula. Thanks for the help in advance
Please open attached file and help me modify the code
I need values from column F in ascending order.
Also I need in column G only the values without formula.
Hi Guys.
I was just wondering, is this possible:
I have a worksheet that calculates the cheapest price of some items. In column B are the item names, in column C are the descriptions, and in column D are the prices.
Is it possible, in a new worksheet, to sort the data into price ascending order?
EDIT: I have Excel 2003, and I'm using Windows 7.
Hi All,
I have code that will sort the table below based on the data selected in F2. I was wondering if anybody has any ideas about how to sort this table both ascending and descending based on the selection. B, C, and D I would want descending, but A would be ascending. As you can see in Column A, the sort does not fill in order as 1.1.10 should be last in ascending order. The only solution I can come up with is to make a helper column and number it backwards. I would give 1.1.1 the value of 10 and 1.1.10 the value of 1 and then sort descending on this column to put column A back in its correct order. In the table displayed I have actually sorted column A manually for this illustration.
Sheet1
*
A
B
C
D
1
POD#
PAT
FREQ
MNHRS
2
1.1.1
80
476
4229
3
1.1.10
0
0
0
4
1.1.2
12
582
7992
5
1.1.3
40
780
6538
6
1.1.4
59
229
7996
7
1.1.5
36
247
1457
8
1.1.6
0
0
0
9
1.1.7
0
0
0
10
1.1.8
0
0
0
11
1.1.9
0
0
0
Excel tables to the web >> Excel Jeanie HTML 4
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ky As Range
If Target.Count > 1 Then Exit Sub
If Target.Address(False, False) = "F2" Then
Select Case Target.Value
Case "POD#": Set ky = Range("A2")
Case "PAT": Set ky = Range("B2")
Case "FREQ": Set ky = Range("C2")
Case "MNHRS": Set ky = Range("D2")
Case Else: Exit Sub
End Select
Application.EnableEvents = False
Range("A1:D11").Sort Key1:=ky, Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.EnableEvents = True
End If
End Sub
Hi Guys
As above really, Is it possiable get cells to automatically sort in Ascending order?
Cheers
Paul
I need to sort an exel worksheet according to the name of the 1 st coloumn(Ex coloumn A) in ascending order .
Ex
question
A | B | C |.. (Columns A,B,C)
link3 | 3 | (data)
link1 | 1 |
llink2 | 2 |
this needs to be sort out like this ..
Answer
link1 | 1 |
link2 | 2 |
llink3 | 3 |
I need to do this in VBA .Can any one help to write the code ?
Thanks in advance
Please see attached doc.
The four macro buttons change the graph to show the specific month. Is there a way where i can sort the bars out so that the longest bar is at the top and the shortest at the bottom, or visa versa.
Like data sorting a table, but sorting the graph instead.
Thanks for your help...
How can I sort cell blocks(below) into ascending order and then rearrange back into 6 columns starting with smallest number in A1, and largest number in F7 using a macro?. Is there any way to do it without lining them up in 1 column and then hit sort ascending arrow and then putting them back into each column?
Thank-you for any help with this.
Bud
A1
208 082 285 081 363 266
091 364 276 101 365 286
156 393 636 166 394 646
176 395 656 378 396 933
209 092 286 210 102 287
219 193 308 220 203 309
221 213 310 237 379 315
|
|
