Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










VBA Auto Sort The Column


I have the below small code which auto sorts information in column A & B but I am having trouble trying to get it to sort from row 3 and not sort the first 2 rows. The first row is the column header the second row I want left blank and then sort after that.

Plus at the moment after I enter something into column A it auto sorts itself but what I would really like to do is have it commence the auto sort after I have entered information in column B and pressed "enter". Because at the moment I enter info in column A then it auto sorts itself then I must then find it and then enter the info into column B.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range
Set rg = Columns("A") 'Column containing names to sort
If Intersect(Target, rg) Is Nothing Then Exit Sub
Range("A:B").Sort key1:=rg.Cells(1, 1), order1:=xlAscending, header:=xlGuess
End Sub


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Auto Sort Macro On Column Changes
How would you right code to automatically sort (using the same sort parameters all the time) when the value of a column changes?

Here's the scenario:
Columns B through N are populated.

The sort parameters are (in order) column F ascending, column D ascending and then column B ascending. The sort in column F is based on a custom list (but I doubt that matters here).

As far as the number of rows to select, it varies but there is always a blank row at the bottom of the selection.

What I am looking for is code that will sort the selection (currently its B4 through N37) in the above order automatically when the value in any of the selected rows changes in column F.

View Replies!   View Related
Auto Sort
I am trying to figure out how to auto sort information on a spreadsheet so that when update notes are added by date at the bottom of the list, they are automatically sorted (moved) to the top of the list so the latest update is shown first. I am pretty clueless when it comes to macros although I have given it some attempts after reading other posts without success. I am including a copy of the spreadsheet. The info that I need sorted starts at B43:E43 and goes to B104:E104. That same sort info would then be used for the similar info to the right of this one. For example everything on the line B43:E43 would move together.

View Replies!   View Related
How Can I Auto Sort
There are only 3 columns, A, B, C and only 10 rows but I would like it to sort automaticaly. I enter data on other sheets and it is loaded automaticaly back to sheet 1 after some calculations are made. I would like the data on sheet 1 to sort automaticaly on column C keeping the rows of data intact.

View Replies!   View Related
Auto-sort In Worksheet
I have a worksheet in Excel which contains scores for a quiz I am organizing. The rows contain the team names, the columns the scores per round per team.
I would like to have the worksheet automatically sort the columns in descending order (so that the winner will come out on top after the quiz has finished), everytime I change something (everytime a score is updated). I can't find anything in Excel which would allow me to do that, so I'm guessing it's only possible in VBA? Unfortunately, I don't know anything about VBA, so I'm stuck.

These are the ranges I would like to have sorted, in order of priority:

Row 1 is the row with the column titles (Round 1, Round 2,...).
C2: C26
N2:N26
M2:M26
L2:L26
K2:K26
J2:J26
I2:I26
H2:H26
G2:G26
F2:F26
E2:E26
D2:D26


View Replies!   View Related
Auto Sort Set Range
I know that I could just click sort, decending, "points" etc but I figure there must be a way of it auto updating.....

Please see the attached file - I have attempted doing it with Ranking Formula but forgot that it would just give me error messages when two have the same points so I got rid of that.

Have tried doing it with other formula found on this website and others, however I appear to be stuck. I might be doing it wrong but as I'm not manually putting in the "points", they are being worked out with =( SUMPRODUCT((H4:H16={"W","D","L"})*({3,1,0}))+58) for example, it does not move their position.

What I would like it to do:

1. People select from drop down list whether they think the team will W, D, L
2. The Table to the right should automatically change the points dependant on the result
3. The Teams position should change automatically in line with their new points total.

View Replies!   View Related
Auto Sort Alphabetically
is it possible to auto sort data using VB script?

as i know i can manually do it but i would like to run a script to sort data on sheet 2

View Replies!   View Related
Auto Sort Code
Need to add an auto sort code to the end of this worksheet module 2, so that after all the dates are figured, it will sort by this date, no matter how many dates there are.

View Replies!   View Related
Auto Sort Of Data
As you can see from the attached spreadsheet. I have a page called Pattern Etch where we enter the data for all the processing done in a single machine.
Each device type is slightly different, and therefore has a different calculation fasctor for its etch time. This all weorks fine. What I had previously was another spreadsheet with the pages b and g in it. This is where the factors are calculated and updated from.

I'd like to make this update automatic, and suspect the easiest way is to combine the two spreadsheets, as in the attached version. Can the sorting of the data from the patternetch page to the apprpriate pages, b and g be done automatically? If so can it be done for many pages as I have cut down the spreadsheet, I actually have 50 variants, each will need its own page (as for b and g).

View Replies!   View Related
Auto Filter Won't Sort
For some reason I have a worksheet that won't sort stuff by Autofilter any more. I built it as a customer database but for some reason today I just can't get the names to ascend or descend. there is something simple I can check for, I can post an empety sheet if needed but right now I can't really post a list of all my customers info.

View Replies!   View Related
Auto-Sort Columns
I am trying to create a VBA macro that autosorts a selection of columns by column A. I currently have the below already written which gives the desired effect. I was just wondering if there was a way i could have this script constantly running on the sheet so it is always sorted correctly without me having to click Run Macro everytime.

Sub Macro1()
Columns("A:F").Select
Selection.Sort Key1:= Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

View Replies!   View Related
Auto Sort Table
I've written a fantasy football programme, however i'm having trouble getting the table to auto sort. alter one of the values in the wins tab, then goto the scores tab to see that they don't auto sort.

View Replies!   View Related
Transferring Data/Auto Sort
I am trying to combine rows of data, each 4 cells long from multiple worksheets into one master sheet. I need to know if there is an easy way to do this and also I would like to setup an Auto Sort if possible to automatically sort the rows by date.

The data that will be transferred is: Date, Amount, Description, and Company Billed. I have my workbook organized by a Vendor per worksheet and would like to have the data I enter inside the vendor worksheet to automatically transfer to the master sheet and sort by date.

View Replies!   View Related
Data Auto Sort By Date
I have a very simple excel with three columns Name (column A), Date (column B), Hours (column C). I want the excel to autosort upon opening so the data is sorted by date. I right-clicked on Sheet 1 and put this in (I found it online):

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Range("B1").CurrentRegion.Sort Key1:=Range("B1"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.ScreenUpdating = True
End Sub

View Replies!   View Related
Auto Sort A List By Values
Is there a way in Excel to have a list of names with values automatically re-sort whenever any of the values change.

The default sort I have in mind is ascending by values.

eg.

CITY VALUE
Sydney 100
Boston 200
London 300
Moscow 400
Houston 500

The values change to:

Sydney 5000
Boston 250
London 8000
Moscow 300
Houston 50

I now want Houston 1st, Boston 2nd etc - automatically - without me having to do a sort again by Value.

View Replies!   View Related
Auto Sort Drop-down List
Is it possible to autosort a drop-down list based on the number of times an item is selected? A drop-down might have 500 lines so it is user-friendly if the top lines are those selected by users the most often.

View Replies!   View Related
Auto Sort Table Without Macros
Saw quite a few threads on auto sorting but nothing that solves my problem: Sheet 1 has two columns, column A has names and column B has numbers. These numbers are manually entered, and there are duplicate values.

Sheet 2 has to present the same names and numbers in ascending sort order. I cannot use macros due to security restrictions. I can accomplish it using a combination of functions LARGE and INDEX, if the values are not duplicated. But that technique fails when there are duplicate values.

In other words, Sheet 1 has:

NAME VALUE
Bob 5
Joe 2
John 5
Sue 3

and Sheet 2 should auto sort these values and give us:

NAME VALUE
Joe 2
Sue 3
John 5
Bob 5

View Replies!   View Related
Auto Sort Macro Code
I have a spreadsheet to create with columns A-I, this will be used by others when I am away and the problem is I dont want them to have to keep going into the data-sort option. Is there a way that I can get the items to auto sort into alphetical and numerical order across the columns? Personally I would be happy with sorting the columns manually each time it's accessed but I know the others using the program won't be!! How would it work if its poss? would it auto sort on saving?

View Replies!   View Related
Auto Sort All Columns Independently
I am looking for some help with a spreadsheet I am working on, the purpose of this sheet is to record case numbers that I am working on.

This spreadsheet will have new information added to it regularly and the cases may not be in a sequential order.

What I have found is a code that will sort a column of case numbers into an ascending order upon opening the file. What I would like to be able to do is apply the same rule to all columns on that sheet.

I have included the current spreadsheet to let you see how far I got.

View Replies!   View Related
Auto Sort A League Table
I have a football pontoon league that works on goals scored, what I am tring to do is to be able to auto-sort the league on the right of the document when I amend the numbers on the table on the left of the document.

View Replies!   View Related
Auto Sort Not Working Correctly
I'm try to get the pos. placing in a auto sorted table to remain in place
i.e. 1st next to the top position. 2nd next tothe next position and so on down. but it keeps getting it wrong! I've tried amending the macro serval times but it makes things worse! Sorry can't attach the file, too big for this forum!!!!

View Replies!   View Related
Auto-sort In Excel(Soccer Table)?
Create a macro for a soccer table.
image : http://i25.tinypic.com/1tk0na.jpg
sort all characteristics (Win,Draw,Lost and and so forth)


Formulas:
D2 =IF(B9>C9;1)+IF(B11>C11;1)+IF(C13
D3 =IF(C9>B9;1)+IF(C12>B12;1)+IF(C14>B14;1)
D4 =IF(C10>B10;1)+IF(C11>B11;1)+IF(B14>C14;1)
D5 =IF(B12>C12;1)+IF(B10>C10;1)+IF(C13>B13;1)
E2 =IF(B9=C9;1)+IF(B11=C11;1)+IF(C13=B13;1)
E3 =IF(C9=B9;1)+IF(C12=B12;1)+IF(C14=B14;1)
E4 =IF(C10=B10;1)+IF(C11=B11;1)+IF(B14=C14;1)................


View Replies!   View Related
Auto Custom Sort List On Certain Days
I have a fairly large watch collection as below which I wear on a daily rotating basis(!).

FORTIS
Seiko
OMEGA SPEEDMASTER
Seiko
SINN 144
Seiko
Accurist
Seiko
TEMPTION
Seiko
OMEGA TRIPLE DATE
Seiko
Junkers....................



View Replies!   View Related
Auto Sort Table Upon Update Of Another Cells
I'm using a total of 20 Rows and 2 Columns. Each row has Column A for Description and Column B for Score. There are total 10 subjects with 10 noneditable rows, and 10 editable rows.

I'd like to protect column A and B for rows 1 to 10 and unprotect only Column B for rows 11 to 20. When any cells from Column B, rows 11 to 20 updates, I'd like to automatically sort columns A+B for ONLY rows 1 to 10.

Therefore, rows 1 to 10 need to be sorted based on the scores on Column B, and since rows 11 to 20 are just input fields, they are never to be sorted.

Column B for Rows 1-10 will be formulas and are based on Column B values from rows 11-20.

When a value in any of the rows 11-20 of Column B is updated, Range A1:B10 will automatically sort from highest score to lowest score (Column B).

Since Column B for rows 1 to 10 contains formulas and not actual values, would it throw off the order of things when sorting formulas?

the macro coding to perform this task.

Here is just a visual of what the spreadsheet will look like

....A B
=======
1||A (B11)
2||B (B12)
3||C (B13)
4||D (B14)
5||E (B15)
6||F (B16)
7||G (B17)
8||H (B18)
9||I (B19)
10||J (B20)
11||A 10
12||B 9
13||C 8
14||D 7
15||E 6
16||F 5
17||G 4
18||H 3
19||I 2
20||J 1

The Bold are editable fields. and I want to use auto sort A1:B10 based on updated values of B11 to B20.

I have also attached the basic excel file that situates my concern.

View Replies!   View Related
Auto Find/Replace And Marco Sort
I'm looking for is something that searches all worksheets in the work book for occurances of A1 (shovel) and replace it with A2 (spades). The list will be about 100 cells long and the idea would be that I could copy a sheet called "replace" with this list in, run it and standardise all the terms in the list within that workbook. Does it sound feasable?

The other query I'm not sure can be done. When I've tidied all these 'names' to a standard one, I want to sort the data (sometimes rows, sometimes columns) alphabeticalled for instance by the A1 to A10, or by A1 to Z1. Simply using the sort option loses all the references as is doesnt change any cells linked to that cell. The only way I could think to make keep the other cells pointing to where they should is to cut and paste/each row/column in alphabetical order manually.

View Replies!   View Related
Auto Sort- Row Refernce Based On Cell
I am typing a number into Cell B4 (ex. zza4582) this number is a serial number that will be in many differnt cells in column "A" on a different sheet "Template Paste". On each row with that serial number I need the information from "A-I" to be copied in the main sheet starting on row 10 and continuing on from row to row depending on how many times the serial number is found.

View Replies!   View Related
Sort By Definition? And Auto-fill Lists?
I update a daily incident report that is sorted by priority of the request as well as the date/time. In the past, I've recorded and run macro's to add a number to a priority so that it can sort numerically. Lately I've been tasked with building similar functionality into more reports. I am wondering whether I can tell Excel what order to sort items into (ie "Sort by HIGH, then MEDIUM-HIGH, then MEDIUM, then LOW)?

With regards to auto-fill lists (for want of the proper name), I report on multiple IT systems that fall into six "system" categories. Is there a way I can set up the "System" column to automatically update the correct data once the IT service is entered, rather than manually look up systems category then click the relevant system in the drop down box?

View Replies!   View Related
Auto Sort Columns On Workbook Open
I have a worksheet with 10 columns, and an ever number of growing rows.

What I would like to do is to Sort Column 'B', along with all the
other respective data in the other columns, each time the spreadsheet
opens. I would prefer to use VBA or some other auto-launching event.

View Replies!   View Related
Auto Sort Values In Drop Down List
i had a list based on dynamic formula.When ever i update(Add or Delete) New items to my List the drop down list should also be updated and sorted in Alphabetical order(Ascending :Words Starting with A - Z). How do i remove the blanks in the drop down list whenever i delete the data in the Source Data.

View Replies!   View Related
Auto Update Formula Reference & Sort
1. Is there any way a Cell can automatically be updated? for example:

On Citi Bank worksheet, As soon as a deposit matures, I insert another row and enter new fixed deposit data on "Row 13", but then I have to manually change the references on "Row 4" so that it points to the new "Maturity date" and the "Interest" and the "Current balance".

Is there any way I can automatically update Maturity, Interest rate and Current balance as soon as I enter new deposit information.

2. On the "Fixed Deposit Report" worksheet. Is there any way the list of deposits can automatically sort based on the maturity date as soon as I enter the new deposit information?

View Replies!   View Related
Auto Sort Values From Highest To Lowest Based Off Of Value
I need the close% column to auto sort from highest to lowest so that I can see at a glance who the top sales person is. I have conditional formatting for the top three but I would rather them auto sort by close%, can anybody help me with this. I have attached the file,

View Replies!   View Related
Auto Sort A Table As Data Is Changed In Another Worksheet
My problem this time is in regards to auto sorting a table as data is changed in another worksheet and is auto copied within the said table.

I.E. I need the Fax page (second last), the table has to auto sort based on column " C ". This is easily done using custom sort but can it be done automatically ? I want to lock the page so nobody modifies it.

I attached a copy of my work so far, using Excel 2007.

All the sheets are locked except " Player DATA " and " Fax ".

The password is " Moose ".

View Replies!   View Related
Sort Column To Match Pair In Other Column, Skip Blank Spaces
Here's what I have.. (on a much smaller scale)

http://www.jmetenterprises.com/produ...pics/excel.jpg

(notice how the lines that match are now even.)

[Edited by admin~ *Link* to large images, don't display them]

View Replies!   View Related
Exclude Column In A Range Sort That Includes The Column
is it possible to leave a column out of this sort code as in A the very first column which just numbers the rows?

Here is my sort ....

View Replies!   View Related
Multiple Date Column To Single Column & Sort
I'm looking for a way to sort dates from several columns into a new single column (perhaps multiple columns if the entry columns become too numerous). I've included an example. There are currently only 4 columns, but there may be as many as 20 in the future, each with 20 dates under each heading. Any blank cells would be eliminated. If I filled a blank with a new date, that date would be placed into the chronological column. So basically, this would take the date from several different categories and create a single calendar of events.

View Replies!   View Related
Auto Fill Column B Based Contents Of Column A
I have a list that is streets and addresses. All contained in column A. Cell A1 is the street name and then Column A3 is the street number. This repeats down column A for almost 1000 street names. I need to fill column B3 with the street name, as well as B4, B5, B6, etc until the street name changes. I was trying to do this with an if..then but couldn't get it to work. I also tried to work on a do.. loop looking for the change from a string to number. But my programming is a little rusty. If anyone can help I was be forever grateful. I mean the alternative is to sit here and copy and paste all day.

ABBEY CT

1700
1700
1703
1703

ABERDEEN LN

1305
1313
1321
1321

View Replies!   View Related
Sort On Column, But Not Include Any Rows With An X In Other Column
I believe that the best way to evaluate this request is to look at the example. I have 2 buttons to demonstrate what I need, along with written instructions.

eMailSampleTest.xls

View Replies!   View Related
Sort Column D Acsending, Then Column C Descending
i want to sort column D acsending, then column C descending. i want to do this using VBA because i cant just format the sheet because another vba macro paste the sheet thier which would rid the formatting. (i would do it myself everytime but it is for someone to make it easier for them and not make mistakes..i searched, just found a sum formula post)

View Replies!   View Related
Sort Column B To Match Column A
column A has random numbers 1- 10. Column B has numbers 1-10 also but in a different order. How can i sort column B to match Column A?

View Replies!   View Related
Sort By Value In Column
if it is possible to sort a range by a specific value in a cell in a column within that range. The reason I'm asking is that I would like to sort by street name in an address line, ie:

500 Aldavar Rd
32 564 Hughes Cresc
36 Aldavar Rd
14 263 Fairlane Ave

If these are samples in a column, I would like to sort, and have the following "output":

36 Aldavar Rd
500 Aldavar Rd
14 263 Fairlane Ave
32 564 Hughes Cresc

it is NOT critical that "36 Aldavar" appears before "500 Aldavar" in the output.

View Replies!   View Related
Sort All Except Those With X In Column F
The below code works perfect, but I want to add to it so that if column F has a x rather than an eMail address, I don't want that row(s) included.

View Replies!   View Related
First/Last Name In One Column, Sort By Last Name?
I have one column of names that has been entered as "first and last name" as one entry and I would like to sort by the last name (the last word in the column). Some of the entries are simply first and last name (e.g. Jane Doe), some are spouses together (e.g. Frank and Elaine Smith) and some contain middle initials (e.g. John Q. and Amy X. Public).

View Replies!   View Related
Type The Product Id In The A Column Of The Orders Sheet(Sheet2) To Auto Insert The Product Name In The B Column
In the first sheet I have two columns, one for the product_id and one for the name of the product. So the Sheet1 is like a small database. The second sheet is for the orders.What I want is when I type the product id in the A column of the orders sheet(Sheet2) to auto insert the product name in the B column so i dont have to write it every time.

View Replies!   View Related
Sort A Column Automatically
Is there a way to automatically sort a column in ascending order while entering numbers so that the column is sorted when last number is entered.

View Replies!   View Related
Sort In VLOOKUP Column
I have a 27R x 19C table (1st row is headings) that I want to sort. The column that I want to sort on (column O) contains a formula that multiplies two values, each of which is obtained by a VLOOKUP formula. The formula in the cell is: VLOOKUP(E3,StrategicImpactTable,2,0) * VLOOKUP(L3,StatuteRegulatoryImpactTable,2,0). Named ranges StrategicImpactTable and StatuteRegulatoryImpactTable each contain two columns, the second of which is a numeric value associated with the text in the first column.

On the first row VLOOKUP(E3,StrategicImpactTable,2,0) evaluates to 3, VLOOKUP(L3,StatuteRegulatoryImpactTable,2,0) evaluates to 2, and the product results in a numeric value of 6. The 26 values in column O are 6, 6, 4, 2, 2, 4, 4, 6, 4, 4, 4, 4, 9, 9, 9, 3, 3, 9, 9, 9, 4, 9, 9, 9, 9, 9.

When I sort the table descending on column O, the table does not sort properly, with no discernable order. Why?

View Replies!   View Related
Count/sort Column
I have 5 columns of numbers and at the bottom of each column I want to list every number in order from least to greatest and how many times it occurred in the column.

I'll just provide you with 5 rows of numbers so I don't waste a ton of space.

0628313841
0814364045
0203193042
0105101518
2021233335

View Replies!   View Related
Sort The Names In Column
Is there a way to have this sheet which is named "sheet1" sort the names in column A which is named "Name" by the data in column B which is named "Rank" automatically as the data in column B "Rank" changes?


A B1NameRank2Jones13Smith64White25Green76Alpha17Baker38Top59Low4

View Replies!   View Related
Sort The Numbers In Column
I have data in column C of rows 25 to 45. The data varies in number form.

What i would like to do is have some VBA code that i can place into a macro which will sort the numbers in column C of rows 25 to 45 putting the highest number at the the top (C25). But i need to move the complete row.


View Replies!   View Related
Two Column Sort And Move
I have a look up table on sheet 1, that looks like this table to the right. On sheet 2, I need it to find the table contents in the column CC, then look at a column AA and find all that match it and move all that match this cell in column AA and copy the row it to sheet 3.

Its 300 lines of data, a couple of time of week.

It is a sort of data with a twist.

AABBCCDDEETreeDogCathousecardavemanboyjobbaseTableboatsamlaurasamjimboydaveweedwalkwalkruncatfunlaughboydivefloatTreeDogCathousecardavemanboyjobbasedaveweedwalkwalkrunfunlaughboydivefloat

View Replies!   View Related
Move To Next Column After Sort
Need the formula that I will need in my macro to move to the next column (up one letter) for the next time I run the macro witout me having to manually change column the letter?



View Replies!   View Related
Sort Column A Including Row 1
I want a macro to sort Column A through B (Sort newest to oldest)that includes the first row. I can record a macro but it doesn't include row 1.

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved