|
Sort Data that Doesn't Have Headers in Ascending Order in Excel
Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assumes that your data does not have headers. This means that the top row of the selected range will also be sorted. Sorting in ascending order means that the macro will sort the data in alphabetical order or numerically ascending order depending on the type of data being sorted. This is a great macro to use to quickly sort a set of data in Excel.
This macro is set to sort by only one field or column in your data. In previous versions of Excel you can sort by up to three columns and in Excel 2007 you can sort by up to 64 columns.
To use this macro, just replace A1:C56 with the range of the entire database or data set that you want to be sorted. Then replace A1 with the column of data that you want to sort by. All other data will be arranged based on the ascending order of this last range reference in the macro.
Where to install the macro: Module
Excel Macro to Sort Data that Doesn't Have Headers in Ascending Order in Excel
Sub Sort_Ascending_Basic()
'Sorts a worksheet in ascending order and assumes there are no headers on the data
Range("A1:C56").Sort _
Key1:=Range("A1"), Header:=xlNo
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.
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.
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 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
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
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
Hi Guys,
I have a sheet, looking like that
product1
product2
product3
Total1
product1
product2
product3
Total2
product1
product2
Total3
What I want to achieve is if the total is positive, then sort the range above it in descending order, if the total is negative then sort t in ascending order, I would like that as a VBA since I need to execute after some other existing code
As in my simple example above I will have multiple range to sort, the cell with total are fixed so they can be 'hardcoded' in the vba, also the amount for the products can be positive or negative.
Thanks a lot!
|
|
