Free Microsoft Excel 2013 Quick Reference

Macro to sort cells

I have a problem with an existing macro. I have attached a workbook with the macro and would love some assistance to get it to work.

Basically the macro should sort cells into the appropriate columns but it only seems to do it for the first five columns.

All help appreciated.

Post your answer or comment

comments powered by Disqus

I seem to have got myself in a muddle and I am hoping that someone can please help.

I recently converted a column in an Excel file to Access. In the process the order of the cells got arranged into alphabetical order. There were comments in the cells, which I transferred for use in the Access database. I have edited and added to this data, so it has changed significantly. I now have to convert the column in the Access table back to Excel and put each cell back in its original order. I have this column back in Excel and have returned the changed data to each cell as comments. I have pasted this in the second column beside the original column in my Excel file. The problem is that the order of column 2 is running A-Z and is out of sync with the first column. This puts it out with all the other columns on the spreadsheet as well. I therefore require a macro that will force the cells and their comments in column 2 back into the same order as the cells in column 1.

Basically I require a macro to sort cells in column 2 so that they match the order of cells in column 1. The cell comments must also be taken into account. No headings to worry about. Data in columns starts at A1 and B1.

Hi Everyone!

I am using Excel 2003 and there is no feature to sort highlighted cell in a column and I know that it exist on Excel 2007. I would like to have a macro to sort highlighted cell in column B.

I appreciate your help and thank you in advance.


could someone help me get this macro to sort all sheets in workbook?

    ActiveWorkbook.Worksheets("ATL AGED AR").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("ATL AGED AR").Sort.SortFields.Add Key:=Range( _
        "H2:H543"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    With ActiveWorkbook.Worksheets("ATL AGED AR").Sort
        .SetRange Range("A1:H543")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    End With
    ActiveSheet.Range("$A$1:$H$543").AutoFilter Field:=8, Criteria1:=">=10000" _
        , Operator:=xlAnd
and instead of going to H543, have it goto the lastrow of that sheet?

any help with this is greatly appreciated.


I have this workbook with many tabs simliar to the example i provided.

I basically need a macro to sort the sheet by V, U, T starting from row 3 and ending before the last vechicle. This is the problem though, the last vehicle ends everytime right before Car # in column A. So in other words in this example i would need it sorted by v u t, starting from 3 down to row 12. It ends at 12 because the next line includes car and a number.

Another sheet might have a longer list of vehicles and might end at row 100 before the next line has car number .

It always starts at 3, but the ending is dynamic where it should stop its selection before "car number ".

I would also like it to work on the current sheet im on, because i have up to 100 sheets like this and would not want it name dependant. Just want to click on a tab, and run a macro and have it sort from 3 down to the line before car in column A.

Another thing i should mention in this example i pasted values, in my real workbook it will contain links, so i everything including Column A which lists the vehicles and year spans will be displayed through a link from another workbook.

In the included file i have what i start off with and end up with to clear any confusion.

Main thing is to have it stop its selection from row 3 down till it runs into a name of Car Number in column A where it stops before that line. Then have it sort by V, U, T. Keeping in mind most of the values in the cells are linked from another workbook.

Thanks for anyone that helps,

Hi all,

I was wandering if anyone can offer any advice on a problem.

Basically, in A:A I have a list of numbers. In B:B I have a list of corresponding values. The values in B:B are always unique, but values in A:A may be the same. Identical values will always appear together (eg A1, A2, A3, etc).

I want to make a macro to sort through this data, and if A1=A2, copy B2 to C1. If A1=A3, copy B3 to A1.

Then move down the list in A:A until it finds the next value....and repeat.

So in essence...where values in A:A are identical, column B:B is copied and then transposed into the first cell containing the value.

If this does not make sense...the diagram below may help explain!

Original list -

1 569
1 572
2 433
3 625
4 744
5 766
5 767
5 792

Sorted List -

1 569 572
1 572
2 433
3 625
4 744
5 766 767 792
5 767
5 792

Thank you in advance for any advice!


I am trying to create a macro to sort descending using visual basic so that I can add it to a larger macro that I have already created. My problem is that when I record the macro, it refers to the specific sheet I am working on. I need it to apply to any sheet.

Here is the code:

     ' sort Macro
    ActiveWorkbook.Worksheets("Sheet2").sort.SortFields.Add Key:=Range("d1"), _ 
    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal 
    With ActiveWorkbook.Worksheets("Sheet2").sort 
        .SetRange Range("A2:D203") 
        .Header = xlNo 
        .MatchCase = False 
        .Orientation = xlTopToBottom 
        .SortMethod = xlPinYin 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Auto Merged Post Until 24 Hrs Passes;

I figured it out

    Range(Selection, Selection.End(xlDown)).Select 
    Selection.sort Key1:=Range("D2"), Order1:=xlDescending, Header:=xlGuess, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Auto Merged Post Until 24 Hrs Passes;

I figured it out

    Range(Selection, Selection.End(xlDown)).Select 
    Selection.sort Key1:=Range("D2"), Order1:=xlDescending, Header:=xlGuess, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have been working on setting up a macro to sort a multiple column list and then subtotal it. However, the macro (when I record it) comes up with a runtime error. If possible, what would the code be to select all of the rows and columns I need, sort them and then subtotal them. Thanks.

In searching the message board, I can see that there are posts regarding macros to sort on more than 3 criteria in excel 2007. I've tried to adapt these to excel 2003 with no luck. Does anyone have a macro for excel 2003 that will sort on the following criteria

Column A (Order:=xlDescending)
Column B (Order:=xlAscending)
Column C (Order:=xlAscending)
Column N (Order:=xlAscending)
Column F (OrderCustom:=6)

My spreadsheet has headers in row 3 and would sort everything below that. (the total number of rows could change from month to month)

Thanks for your help!


I am trying to assing a Macro to one cell that has a validation list (options: yes and No)

The goal is to run a macro everytime the user selects yes or no.

Thanks in advance for your help

New to the forum, hopefully you all can help!
I would like to create a macro to sort up to 100 hands of 5 card poker. I've tried some different ways but all seem error prone and overly complex.

any ideas, suggestions?


I would like to assign a macro to a cell so that when that cell is populated
the series of formattting is applied to other cells. Can I apply a macro to
a cell?

Hi, this is my first time trying to create a macro and Im stuck. I want to create a macro in an excel template, that will sort a table of data. At the end of the table there are some calculations that I do not want to sort. The problem I seem to have is that each document I create from the template will have a different amount of data in it. Thus If my original macro sorts information in rows 8-53 for example, and in a document created from the template I only have 10 rows of data, the macro will still attempt to sort up to row 50, and thus it attempts to sort the calculations at the end, and it gets messed up.

Here is the Macro Code:

Sub SortColor()
' SortColor Macro
' Macro recorded 7/12/2006 by Indra S.

Selection.Sort Key1:=Range("A8"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

The range "A8:I53", is the maximum amount of data I would need to sort. But lets say I only have data in "A8:I30", After I delete rows 31-53, the range in the macro stays the same, and all my calculations at the end are sorted.

My question then is, How can I get the macro to sort a variable amount of rows? Is this even Possible? Am I being clear enough in my explanation? Any other suggestions?


Hi could someone please help me with this?

I am looking for a macro to sort my colums in ascending order the range i need sorted is from A7:F107, it will be sorted with the numbers in the A column ie 1 - 107. sometimes different numbers get added and it has to be sorted in number sequence.

If possible i would also like a macro to protect this range after the sort,

I need some help creating a Macro to merge cells based on info in another

Here is what I have: Sheet 1 - Customer (column A), Ad Size (Column B),
Page Number (Column C), Position (Column D).
What I want it to do:
If on Sheet 1 - b=Full Page, c=1, d=Full - Merge cells on Sheet 2 a2:b3 and
enter Value from Sheet1-A1...
or if on Sheet 1 - b=Half Page, c=1, d=Top - Merge cells on Sheet 2 a2:b2
and enter value from Sheet 1-A1...
or if on Sheet 1 b=Half Page, c=1, d=Bottom - Merge cells on Sheet 2 a3:b3
and enter value from Sheet 1-A1...

Then if Sheet 1 c=2 use Sheet 2 c2:d3 to merge instead...

On sheet 2
(if Sheet 1 - a1=Village Green, b1=Full Page, c=1, d=Full), Merge a2:b3 and
enter value "Village Green"...

(if Sheet 1 - a2=Market Square, b2=Half Page, c=2, d=Top), Merge c2:d2 and
enter value "Market Square"....

(if Sheet 1 - a3=Briar Manor, b3=Half Page, c=2, d=Bottom), Merge c3:d3 and
enter value "Briar Manor"...

Hello all,

How can I assign a macro to a cell. What I want to do is be able to, for example, click on cell B2 and have some text appear on cell B10. Is that possible? If so can you help, thank you.

Hi All,
I want to copy a macro to several cells with the results showing next to the
marks grades
85 A
75 B
65 C
50 D
instead of me running the macro every time for each student.

I have recorded a macro to sort within a range and have assigned it to a
forms-button. It runs well while executing on XP machine. But people who try
to run it from their 2000 OS get the following error.
"Can't execute code in break mode"
I am not able to reproduce this error on my XP machinee. Please help

Hello, I need a macro to sort based on a criteria. I would like to sort just the "Tom" with a macro. Here is an example:

So the macro would cover range A2:B3 but this will change week to week.
Tom 1
Tom 2
Tom 1
Jim 2
Jim 1
Jim 2

This is my first post, I'm glad I found this forum.

I need to create a macro to sort a .csv into a readable format, like a report. The .csv contains data related to users and various stats attributed to each user. I want to be able to run a macro to generate a form with employee names and columns with each stat that I can conditional format as I need.

We have something like this already that was created a few years back by another employee that takes three .csv files and generates one easy to read report, but its fairly complex and we have yet to dissect it with any success. I will need to build this from the ground up.

I sense there is no easy answer to this, I was hoping someone could just point me in the right direction. I'm sure I can figure it out but I don't know where to begin looking.



Hi All,
I need a macro to sort data by columns in a worksheets but im lost.

I have posted in the general forum here

Any help would be appreciated.

I've recorded my macro but do not know how to assign it to a cell? I can assign macro's to buttons but I'm not find how to assign a macro to a cell.




I am trying to write a macro code that sorts certain rows based on if those rows have blank cells in column G. I have figured out the code to the point where it selects the rows where column G is blank, but then when I want it to sort these rows, it references a set cell. The problem with this is that I want to be able to reuse the macro without having to update it. The rows with blank cells in column G change on a weekly basis (one week it might be row 12 where the blanks start, the next week it might be row 18 where the blanks start).

Is it possible to have the sorting not reference a set cell?

Application.Calculation = xlCalculationManual 
Dim rng6 As Range, ix6 As Long 
Set rng6 = Intersect(Range("G3: G700 "), ActiveSheet.UsedRange) 
If rng6 Is Nothing Then 
    MsgBox "nothing in Intersected range to be checked/removed" 
    Goto done6 
End If 
For ix6 = rng6.Count To 1 Step -1 
    If Len(Trim(Replace(rng6.Item(ix6).Formula, Chr(160), ""))) _ 
    = 0 Then rng6.Item(ix6).Delete (xlUp) 
Application.Calculation = xlCalculationAutomatic 
On Error Resume Next 
Columns("G:G").SpecialCells(xlCellTypeBlanks).EntireRow.Select 'SELECT ENTIRE ROWS
Selection.Sort Key1:=Range("S9"), Order1:=xlAscending, Key2:=Range("C9") _ 
, Order2:=xlDescending, Key3:=Range("D9"), Order3:=xlDescending, Header:= _ 
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you!!


I have some data:

******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2C2B3C3B5C5B6C6B8C8B9C9B10C10B12C12B13C13B15C15B16C16B17C17B19C19=
ABCDE1 Label B Label CSort REF 2 text1text1  3 text1text1  4   A3 5 text2text2  6 text2text2  7   A2 8 text3text3  9 text3text3  10 text3text3  11   A1 12 text4text4  13 text4text4  14   A3 15 text5text5  16 text5text5  17 text5text5  18   A2 19 text6text6  20   A1 21     22     Sheet1 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

And I would like a macro which will cut and insert entire rows to sort the data like this:

******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2C2B4C4B5C5B6C6B8C8B9C9B11C11B12C12B13C13B15C15B16C16B18C18B19C19=
ABCDE1 Label B Label CSort REF 2 text6text6  3   A1 4 text3text3  5 text3text3  6 text3text3  7   A1 8 text2text2  9 text2text2  10   A2 11 text5text5  12 text5text5  13 text5text5  14   A2 15

Hey everyone,

I'm sure I saw a Macro once, that allowed me to sort by font colour.

For instance, all negative values are red, and I wanted to sort by all the
red values in any particular order.

Could I do a sort, using a macro, by cell colour. For instance a worksheet
contains rows of red blue, and yellow. If I wanted to see all yellow, what
would I do?

Thanks in advance for your time and help.

No luck finding an answer? You could always try Google.