Sort Routine Macro for Excel Worksheet

I have an excel that requires a sort routine.  I tried using the start/stop 
recording.  The code is shown below, however, I need this routine to work 
when you don't know how many rows are in the data.  My method has the 
filename hardcoded too.  I don't know if that matters.  This one subroutine 
of many.  Can someone show me how to make this routine more dynamic.  It 
will always sort by the same Column but it needs to work with all different 
Ranges of data (this example is hardcoded to sort data from row 2 through 
row 613.

Sub SortCategoryAssistance()
    Cells.Select
    ActiveWorkbook.Worksheets("NEEDWest Error Report").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("NEEDWest Error Report").Sort.SortFields.Add 
Key:= _
        Range("P2:P613"), SortOn:=xlSortOnValues, Order:=xlAscending, 
DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("NEEDWest Error Report").Sort
        .SetRange Range("A1:T613")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub
 

0
JCO
6/7/2010 5:31:30 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
2311 Views

Similar Articles

[PageSpeed] 29

never mind.  I figured it out as shown below:
'
' SortCategoryAssistanceWithHeadersMacro
'
'
    Cells.Sort Key1:=Range("P2"), _
    Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
    MatchCase:=False, Orientation:=xlTopToBottom
    '
    Range("A17").Select


"JCO" <someone@somewhere.com> wrote in message 
news:OHGNSHoBLHA.5584@TK2MSFTNGP06.phx.gbl...
> I have an excel that requires a sort routine.  I tried using the 
> start/stop recording.  The code is shown below, however, I need this 
> routine to work when you don't know how many rows are in the data.  My 
> method has the filename hardcoded too.  I don't know if that matters. 
> This one subroutine of many.  Can someone show me how to make this routine 
> more dynamic.  It will always sort by the same Column but it needs to work 
> with all different Ranges of data (this example is hardcoded to sort data 
> from row 2 through row 613.
>
> Sub SortCategoryAssistance()
>    Cells.Select
>    ActiveWorkbook.Worksheets("NEEDWest Error 
> Report").Sort.SortFields.Clear
>    ActiveWorkbook.Worksheets("NEEDWest Error Report").Sort.SortFields.Add 
> Key:= _
>        Range("P2:P613"), SortOn:=xlSortOnValues, Order:=xlAscending, 
> DataOption _
>        :=xlSortNormal
>    With ActiveWorkbook.Worksheets("NEEDWest Error Report").Sort
>        .SetRange Range("A1:T613")
>        .Header = xlYes
>        .MatchCase = False
>        .Orientation = xlTopToBottom
>        .SortMethod = xlPinYin
>        .Apply
>    End With
>    Range("A1").Select
> End Sub
>
> 
0
JCO
6/7/2010 9:25:06 PM
Reply:

Similar Artilces:

Import excel data to outlook calendar
I have found lots of tips to import excel data to the address book, etc, but can't find how to "custom map" or how to import data from an excel spreadsheet into the outlook calendar. Could anyone make any suggestions? Hi Tracy, normally you are in the wrong newsgoup, but I try to help you. - First export the dates from your OL calender to an excel file. - In this file, you can find all the headlines for importing. - If you try to import date, be sure that the headline matches as described before. - Then to the normal job for import in Outlook -- Ich hoffe, das hilft / ...

Excel ODBC driver fails to update XLS if a formula exists
Searching for an answer via Google and Bing didn't help, so I'm asking here. I'm using an old version of Visual Studio but I think the problem is in the ODBC driver for Excel. I found code on the Internet for a class called CSpreadsheet which successfully inserts columns into an empty Excel XLS spreadsheet just like I need, but when I try to do the same thing to an existing spreadsheet that contains even one formula like =SUM(A1:A12) none of the cells I try to update accept the values. The non-fatal error out of the Commit() being done says: Cannot delete spread...

Picutres in Excel Cell
I've been trying to insert a picture into a cell and can only insert it into the sheet. I've seen on this board that a cell can only contain text or a formula. Is there any way to get a picture to be associated with the cell such that if the rows are sorted the pictures sort with them? I can do this in Word tables, but Excel offers some features I want to utilize. Kind Regard Hi You can insert a picture in a cell.This will insert a picture into a single cell or a merged cell. Just change the range address and picture path/name to suit. Dim Rng As Range With Work...

Sort data from every workbook in a folder into sheets in another?
I have a workbook named RetailClientTemplate which contains sheets named by locale abbreviation (EN, ES, DE, IT, etc.). I have workbooks named RetailProjectLog_DATE generated each day that are saved into a specific folder location. Each row in each RPL workbook has a locale in column A that matches the name of one of the sheets in the RCT workbook. What I need to do is make a macro that will copy & paste the rest of the data from each row in each of the RPL books (sheet1, range B4:Q200, and col P data separately) in the specified folder into the corresponding sheet for each l...

missing data in excel cells
Hi all. When i print an excel document, i realise that 2 cells have data that is not printed out. However when I use print preview, i do not find any missing data. I am using excel 2003. How can i solve this? ...

setting the "tab" key in excel 97
Can you set how many cells the "tab" key moves the cursor over in excel?? In Office 97 when I hit my Tab key it moves the cursor 16 cells to the right instead of just one. You can do it by locking the cells you don't want to tab to an unlocking the cells that you do want to tab to and then protect th sheet -- Cutte ----------------------------------------------------------------------- Cutter's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=984 View this thread: http://www.excelforum.com/showthread.php?threadid=26866 Tools|options; open Transi...

macro problem. Upgraded to Excel 2002, now getting error message
Hello, this is my first time posting so please go easy on me. I am running a macro that pulls data from several files an interpolates information; the process takes about 20-25 mins. upgraded to excel 2002 and now the when I run a large input file i runs about 2/3 of the way then gives me the standard error messag saying the program needs to shut down. If i cut the file up in smalle parts the program will run through, but it's a pain stitching th output back together. I have never had this problem before when I wa running Excel 2000. Is there a setting I have to change? -Thanks :conf...

Sorting protected worksheets
Greetings! I need to allow others to sort my worksheet which contain a few protected columns. I am also using setup data validation and several other functions. This file is stored on a shared drive. So farI have tried the following: Tools/protection/protect sheets Select locked cells select unlocked cells select format cell, select columns select rows SELECT SORT Select Autofilter Select pivot reports Whenever other users attempt to sort this worksheets, they get a message stating this worksheet is protected. By selecting the "SELECT SORT" option listed above, shouldn't...

Excel printing problem #4
Recently I was working on an Excel file with over 15 worksheets (from different contributors). I encountered problem with the print preview and actual printing: the image shown on the computer screen and then on Print preview (subsequently on the printed documents) was so different. After I finished with checking my data, I adjusted the column widths and row heights on all worksheets, set page margin etc... so they could be printed nicely. But when I switched to print preview, all rows and columns got screwed up, some text in a single line in the cell were cut off, while other text were...

get access form in excel
Hello, i've created a excel menu and i would like to call the access form i've created how could i could that? thanks i don't think you can do that. you will have to create the form in excel. sorry. >-----Original Message----- >Hello, > >i've created a excel menu and i would like to call the access form i've >created > >how could i could that? > >thanks >. > ...

How to do this with macro?
I have several cells in one column, what include numbers (example B2-B4). After few empty cells, I have a cell (example B7), what include formula B4-A1. A1 B1 1 2 3 4 5 6 xx =B4-A1 Sometimes I add new numbers after cell B4 (example B5, B6 etc..) and sometimes I have to add new lines between last used cell, what include numbers and cells, what include the formula (example B7). When I add example three new line before B7, formula moves in cell B10. On the A7 is value xx. If B7 moves B10, also A7 moves A10. After changes example look like this:...

Excell will not take data
I am working on a file on the excell program and it will not accept data. It is as if the cells are blocked but I can't even bring up a new sheet. As a matter of fact, I can't even use my Word Hi have you tested your file is not protected? Do you get an error message then you try to enter data? If yes whcih one do you get? -- Regards Frank Kabel Frankfurt, Germany Alicia Contreras wrote: > I am working on a file on the excell program and it will > not accept data. It is as if the cells are blocked but I > can't even bring up a new sheet. As a matter of fact, I > ...

Invalid Characters in Excel XP Worksheet Names?
Does anyone have (or can point me in the direction of) a list of invalid characters for Excel XP Worksheet names? Thank you Kristin Did you try Excel's Help? I found the following (Excel XP) by searching for 'names' and looking at the topic 'about labels and names in formulas'. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Guidelines for names What characters are allowed? The first character of a name must be a letter or an underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters. Can names be cell references? Names cann...

Display Control Characters in Excel 2007
Hi, This is a little esoteric but I would like to import and edit text files that contain control characters such as <TAB> and <LF> in Excel 2007. I have always been able to do this in Excel 97 and, at work I can do it in Excel 2007 Enterprise running on XP Pro. I recently installed Office Enterprise 2007 at home on XP Home and Windows 7 Home Premium from downloads under the Home Use Program. I can't view or edit control characters in Excel. They are there and I can process them using string functions but I can't see them and, if I try to edit cells that ...

How to use an Excel 1997 files with line chart that have date & ti
I have many old Excel 1997 files with line graphs that show up the x axis date and time values. The old Excel 97 show about ever 10 or 20th date/time stamp in the x axis, but the new 2003 show only black spots at the bottom of the line graphs. How do I get Excel 2003 to produce a line graph to show time/scale data that has dates and times like the old Excel 97 did? ...

Conditional Formatting
I have created my own conditional formating. Condition 1 Cell value is greater than =$F$14, colour red Condition 2 Cell Value is greater than =$G$14, colour orange Condition 3 Cell Value is equal to 0, colour blue I want to apply this to the entire Q column so that it applies to all: Condition 1 Cell value is greater than =$F$15, colour red Condition 2 Cell Value is greater than =$G$15, colour orange Condition 3 Cell Value is equal to 0, colour blue At the moment all of column Q is looking for F14 and G14? Any help on this would be greatly appreciated. Thanks. Neil. ...

not delete worksheets from names in a range
Is there anyway to delete worksheets where one worksheet contains a range with the names of worksheets I DONT want to delete? i.e I want to have a code that says "do not delete worksheets in the range but delete all other worksheets" You can create a subroutine in vba like this: Sub delete() 'Tell Excel to not prompt the deletion confirmation Application.DisplayAlerts = False For Each sh In ThisWorkbook.Sheets forDeletion = True ' Sheet1.Range("A1:A3") refers to the range of cells that ' hold the sheet needs we don...

Footer values on multiple worksheets
Is there any way of placing re-curring values in footers for workbooks with multiple sheets. For example a version number or project title. I do not want to maintain the value in each footer ...

is this possible on excel?
I use a spread sheet to do work schedual 12 work sheet 1 for each month columes are total days for the whole month( i use 4 extra columes between weeks for extra info.) rows are name of staff I want to add 1 more work sheet to keep track of vacation time Is there a formula or way so it will automatically mark on the schedual the vacation time as entered in the vacation work sheet on the proper month and day? Maybe something along the lines of this set-up .. Assume you have in Sheet1, in cols A to D, data in row2 downwards, the set-up: Name...........Reason....1st day of abs...Last day of ...

excel hangs on start up
when click on excel or microsoft word icon, 2 windows pull up, but the whole computer freezes for 1 to 2 minutes and then the second window disappear, but the document open on one of the window only. office 2000 was working fine, till i install the office 2003 as an upgrade. i appreciate if someone can help me solve this problem. ...

Unlinking from another Excel Workbook
I have a problem with documents that I have created, then copied to be used on another computer and then put back into my computer. When I make a copy, it ends up linking that to another file...half the time it is one that was on a floppy disk. I don't want this link between worksheets, but it prompts me every time I open the file if I want to update info. I always say no. Is there any way to remove this link, and, in the future, prevent this from happening? If I go into the "links" under edit, it does not give me an option to remove this link, only update or change it. How can ...

Excel
1. An Excel tabulation begins at Row 6 ; and then, Cell G6 is subject to Conditional Formatting as follows :- =AND(ROWS($G$6:G6)<=COUNTIF($B$6:$B$35,$B$6)+COUNTBLANK($G$6:G6),G6<>"") 2. Subsequently, the Conditional Formatting of Cell G6 is "Paint-Brushed" to the succeeding cells. 3. And there, a blank row is inserted over Row 6 ; Look at the Conditional Formatting again, it has changed as follows :- =AND(ROWS($A$1:$G$7)<=COUNTIF($B$7:$B$36,$B$7)+COUNTBLANK($A$1:$G$7),G6<>"") 4. Now, is that change justifiably outrageous ? Expected, or what ...

Excel 2007 Bug .XLK Auto Recovery
Auto Recovery is suppose to save a temp file to C:\Documents and Settings\[username]\Application Data\Microsoft\Excel. I set it up in Excel Options to save the Auto Recovery files in that location. However, when I close an Excel 2007 file (that I've saved in My Documents), there is an extra file (in My Documents) with a "Back of" prefix followed by the filename. Three issues: 1. It's not temporary, 2. It's not in the correct location and 3. I don't wish to uncheck the Auto Recovery; I'd like Excel 2007 to work as advertized. Thanks! -- John The "Bac...

Find differences between Excel worksheets / Version Control/ Excel Diff software
DiffEngineX is a utility that finds the differences between the formulae and constants contained in either two whole Excel workbooks or selected worksheets. It generates a cell-by-cell difference report in a new workbook and color highlights different cells in automatically made copies of your workbooks. As Excel spreadsheets get modified over time new rows and columns can be inserted between existing ones. DiffEngineX can align both similar rows and columns in the sheets being compared. It has many powerful features such as the ability to hide matching rows, group like changes to adjacent ce...

Excel 2002 hangs when I try to insert a Package
I have Office XP And SP 3 installed. When I try to insert a package using Insert > Object > Package Excel hangs and stops responding. I can succesfully insert a PowerPoint object type... ...