Free Microsoft Excel 2013 Quick Reference

Macro to Sort Table

I have developed a following Macro which Sort a Point Table based on the two criteria.

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
    Range("B4:H11").Sort Range("G5"), xlDescending, Range("H5"), , _
        xlDescending, Range("B5"), xlAscending, xlYes
    Range("K4:Q11").Sort Range("P5"), xlDescending, Range("Q5"), , _
        xlDescending, Range("K5"), xlAscending, xlYes
Application.ScreenUpdating = True
End Sub

The above macro is defined in Sheet (B) to Sort Point Table which is linked to the Data Table on different sheet i.e. Sheet (A). When we switch to Sheet B wherein macro is defined this macro works absolutely fine and sort the Point table based on the changes in the data table.

I just want to amend this Macro in such way that it if my Data Table and Point Table are on the same sheet this macro automatically sort the point table based on the changes in data table.


Post your answer or comment

comments powered by Disqus
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.
'

'
Range("A8:I53").Select
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?

Thanks.

Hi,

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.

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:


	VB:
	
 sort() 
     '
     ' sort Macro
     '
    ActiveWorkbook.Worksheets("Sheet2").sort.SortFields.Clear 
    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 
        .Apply 
    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


	VB:
	
 sort() 
    Range("D2").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.sort Key1:=Range("D2"), Order1:=xlDescending, Header:=xlGuess, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
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


	VB:
	
 sort() 
    Range("D2").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.sort Key1:=Range("D2"), Order1:=xlDescending, Header:=xlGuess, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortNormal 
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.

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.

RNF

Hello,
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)
then
Column B (Order:=xlAscending)
then
Column C (Order:=xlAscending)
then
Column N (Order:=xlAscending)
then
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!
Becky

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

Code:
    Cells.Select
    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:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("ATL AGED AR").Sort
        .SetRange Range("A1:H543")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("H1").Select
    Selection.AutoFilter
    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.

TIA

Hi
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?

Thanks!
Kevin

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 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,

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 -

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

Sorted List -

A B C D
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!

Robert

Hi
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.
A B
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.

Thanks,

Andre

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 http://www.excelforum.com/excel-gene...y-columns.html

Any help would be appreciated.

I wonder if anyone can help me with this one.

I have a table , A8:V36. Peoples names appear from cells B9:B36.
Dates are enterd from C8:V8 with a range name defined as 'Date'.
Integer values appear in cells C9:V36

I want a macro to be able to sort the table by selecting one of the dates and sorting in descending order of value against each person for the date just highlighted.

I hope I have explained the situation clearly enough. I have been able to produce a macro by the Developer , Learn process but that was for one specific date not for a variable date which is what I am enquiring about.

As a supplementary question , anyone know of a good easy to follow text book from which to learn about macros for Excel ?

Hello everyone please could someone take a look at a macro for me?
I have a list of letting agents that I need to sort into a table to import into Access. The list is dynamic in that not all the fields are present for every record.
I will have over 4000 records to sort out, so I would really like to automate this.
If possible the macro should read rows from the data worksheet and write to columns and rows on the Table worksheet.
I have attached a small example of the data and output required.
To make it a little more challenging the column header name is part of the data and will need stripping out as well.

Thanks for looking.

Regards
Andrew

Lettings Agents.xls

Hi,

I have 8 columns of data with each column containing approx 130 rows of information.

In the 8th column there is an IF function in every row that checks criteria and gives a result 'OK' or 'NOT OK'

Now what i would like to do is create a macro that sorts the whole table in terms of OK/NOT OK, so that the OK results are at the top of the table. Then after being sorted it copies all the OK data to another table.

This all sounds simple however, sometimes there might be 10 rows of OK data and other times there might be 50, and i need excel to identify all the rows of OK data to be copied to the other table.

So the problem i have is trying to tell excel to look for all the OK data and not just the first few lines.

This is pretty hard to explain so if you need further clarification please let me know.

Cheers

Hello,

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
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

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

Hello All,

I am trying to create a capability where a user of my spreadsheet would be
able to choose the primary sort key via a drop-down box (eg last name, first
name, etc) and then have the spreadsheet sort automatically. This drop down
box is located in cell b12. My table has headings(a1313) matching the
options in the drop down box. I have parts of the macro written, but I am
having trouble tying it all together. Basically, I would like the macro to
look at cell b12, match it with the appropriate heading and then sort
primarily based on that column of information. Below is an example of what I
have been working on (although it does not work)

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Target.Address = "$B$12" Then

If Range(B13).Value = Target.Value Then

Range("A14:P25").Select

Selection.Sort Key1:=Range("B14"), Order1:=xlAscending, Header:=xlGuess,
_

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End if

If Range(D13).Value = Target.Value Then

Range("A14:P25").Select

Selection.Sort Key1:=Range("D14"), Order1:=xlAscending, Header:=xlGuess,
_

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End if

If Range(E13).Value = Target.Value Then

Range("A14:P25").Select

Selection.Sort Key1:=Range("E14"), Order1:=xlAscending, Header:=xlGuess,
_

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End if

If Range(H13).Value = Target.Value Then

Range("A14:P25").Select

Selection.Sort Key1:=Range("H14"), Order1:=xlAscending, Header:=xlGuess,
_

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End if

If Range(I13).Value = Target.Value Then

Range("A14:P25").Select

Selection.Sort Key1:=Range("I14"), Order1:=xlAscending, Header:=xlGuess,
_

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End if

If Range(J13).Value = Target.Value Then

Range("A14:P25").Select

Selection.Sort Key1:=Range("J14"), Order1:=xlAscending, Header:=xlGuess,
_

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End if

End Sub

I would appreciate any suggestions on this little dilemma.
Thanks in advance,

A.

Hi there, I am looking for a way to determine if data in a column is sorted (by any field) and run a macro if sorting is applied.

I have a Table (Employee_Table)which is used for storing employee information (clocking ref number, pay rate etc.) which is in, say, Sheet1 ("Employees"). In Sheet2, I have a roster which is prepared a week in advance, printed out and put up for staff to see and also contains hidden columns that show costing information for each staff. So, the manager can see the cost change to the company of altering staff's rostered time.

The first column of the Table in Sheet1 is "Employee Ref No". The pay rates are pulled in to Sheet2 using Vlookup of the Table in Sheet1 (I couldn't get index/match to work with my table, am happy to use Vlookup for now). My problem arises where the table is not sorted by "Employee Ref No"; the Vlookup doesn't work. The user needs to be able to sort the table to find employee info so disabling sorting on Sheet1 is not an option. Therefore, I have a macro based on the Worksheet_Deactivate event of Sheet1 which re-sorts the Table by "Employee Ref No". Is there a way for the macro to run only if the Table is not already sorted by "Employee Ref No"? Or have you any other suggestions/workarounds on what would accomplish my goal? Apologies if 'no. of words I've used' > 'words needed to explain problem'.

My Macro as is;

	VB:
	
 Worksheet_Deactivate() 
     ' Sort by Clock No. Smallest to Largest in order for Vlookup to work.
    Sheets("Employees").Unprotect 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Sort. _ 
    SortFields.Clear 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=1 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=2 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=3 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=4 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=5 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=6 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=7 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=8 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=9 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=10 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=11 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=12 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=13 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=14 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=15 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=16 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=17 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=18 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=19 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=20 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=21 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=22 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=23 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=24 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=25 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=26 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=27 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=28 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=29 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=30 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Range.AutoFilter Field:=31 
    ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Sort. _ 
    SortFields.Add Key:=Range("Employee_Table[[#All],[Clock No. ]]"), SortOn:= _ 
    xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 
    With ActiveWorkbook.Worksheets("Employees").ListObjects("Employee_Table").Sort 
        .HEADER = xlYes 
        .MatchCase = False 
        .Orientation = xlTopToBottom 
        .SortMethod = xlPinYin 
        .Apply 
    End With 
    Sheets("Employees").Protect Password:="test" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Here is a summary:
Sheet 1 - Table
Sheet 2 - Roster which uses Vlookup to lookup data based on Ref No in Column1 of Table
Desired - If Table sorted by Column1 -- do nothing, else -- run macro to sort by Column1

Thanks and Regards,
Ciaran

Hello Again All,

I have a table of colour values (12191 rows...) and a colleague gave me a macro to assign the cell colour based on RGB values in 3 columns. The macro is in the attached worksheet

I now want to sort the cells by the cell colour. but there are so many colours that the standard sort options aren't working. Is there a way to sort them by the colour so that when I scroll down the list there is a gradual change in colour. I have attached an extract from the table for people to have a play with.

Best Regards

Jarvice

I am using a table to select a value. Either 1,2,3,4,5,6,7,8,9,10.
Then based upon what value is how I would sort 7000 lines (rows) of data. There are 5-6 columns. I have a macro that I can use to sort depending on which value but I was hoping that It would do it automatically.

Like if I had selected 3 then it would sort first on column C.

Right now I can manually select a macro that I have but to do this automatically would be great.

I have a huge list that It would be easier to find an item if I first sorted based upon a criteria.

Something like: If C=1 then run macro 1, If C=2 then run macro 2, etc...

Thanks - I have came along way so far. Thought to automate would be nice.

Hi, I need some help. I've been searching the internet for the last 5 hrs and I can't seem to find the answer to this basic VBA question.

I have a table called table3, and table3 has 7 columns (columns "A" thru "G") starting in row 10. I want to create a macro to delete the table row or table rows based on a text value in column "G". Let say that text value is the word "delete". So where ever "Delete" is typed I want a macro to delete those table rows that has "Delete" at the end of the table which is column "G". Any help will be greatly appreciated.


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