Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 VBA Sort without Select
Hi all!
If it is possible, can somebody give me the syntax for sorting a worksheet without selecting it? The worksheet has a header row (row 1). I would like to sort all the values in the worksheet by column A Ascending. Column A is a Date. There will never be more than 50 rows and 10 columns. This is the code I'm using. Sheets("Accum").CurrentRegion.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom The error I'm getting is the method or object isn't supported. Any help greatly appreciated. Thanks in advance. cheers, Matt. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 VBA Sort without Select
Hi all!
I've changed my sort code to as follows. Now I'm getting an error that states: The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank. Sheets("Accum").Range("A2").CurrentRegion.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Again, any help greatly appreciated. Worksheet setup still the same. Thanks again for any help. cheers, Matt. "Matt." wrote in message ... Hi all! If it is possible, can somebody give me the syntax for sorting a worksheet without selecting it? The worksheet has a header row (row 1). I would like to sort all the values in the worksheet by column A Ascending. Column A is a Date. There will never be more than 50 rows and 10 columns. This is the code I'm using. Sheets("Accum").CurrentRegion.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom The error I'm getting is the method or object isn't supported. Any help greatly appreciated. Thanks in advance. cheers, Matt. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 VBA Sort without Select
Dim ws As Worksheet
See if this helps. I'll be glad to explain how it works if you need m to. - Pikus Set ws = Worksheets(1) For x = 2 To 50 alph = ws.Cells(x, 1).Value a = x + 1 z = x For y = a To 600 If ws.Cells(y, 1).Value < alph And ws.Cells(y, 1).Value < " Then alph = ws.Cells(y, 1).Value z = y End If Next y If z < x Then ws.Rows(z).Cut ws.Rows(x).Insert End If Next -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 VBA Sort without Select
Matt,
The problem is that the Range in Key1 points to the ActiveSheet, not the Accum sheet. Try rewriting the code as follows. With Sheets("Accum") .Range("A2").CurrentRegion.Sort Key1:=.Range("A2"), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With Note the period before both Range("A2") references. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Matt." wrote in message ... Hi all! I've changed my sort code to as follows. Now I'm getting an error that states: The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank. Sheets("Accum").Range("A2").CurrentRegion.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Again, any help greatly appreciated. Worksheet setup still the same. Thanks again for any help. cheers, Matt. "Matt." wrote in message ... Hi all! If it is possible, can somebody give me the syntax for sorting a worksheet without selecting it? The worksheet has a header row (row 1). I would like to sort all the values in the worksheet by column A Ascending. Column A is a Date. There will never be more than 50 rows and 10 columns. This is the code I'm using. Sheets("Accum").CurrentRegion.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom The error I'm getting is the method or object isn't supported. Any help greatly appreciated. Thanks in advance. cheers, Matt. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 VBA Sort without Select
Thanks Pikus.
I understand the sorting algorithm. I just was hoping to use Excel's built in method without using .SELECT or .ACTIVATE, because everything I've read says the Selecting elements unnecessarily before working with them slows the app down. But an algorithm like this will probably be just as slow as selecting or activating the sheet I want to sort anyway. If my assumption is wrong, please let me know. cheers, Matt. "pikus " wrote in message ... Dim ws As Worksheet See if this helps. I'll be glad to explain how it works if you need me to. - Pikus Set ws = Worksheets(1) For x = 2 To 50 alph = ws.Cells(x, 1).Value a = x + 1 z = x For y = a To 600 If ws.Cells(y, 1).Value < alph And ws.Cells(y, 1).Value < "" Then alph = ws.Cells(y, 1).Value z = y End If Next y If z < x Then ws.Rows(z).Cut ws.Rows(x).Insert End If Next x --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 VBA Sort without Select
Thanks Chip!
This is exactly what I was searching for. (To anybody else who might use this: Don't forget the <space <underscore after MatchCase:=False, ) cheers, Matt. "Chip Pearson" wrote in message ... Matt, The problem is that the Range in Key1 points to the ActiveSheet, not the Accum sheet. Try rewriting the code as follows. With Sheets("Accum") .Range("A2").CurrentRegion.Sort Key1:=.Range("A2"), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With Note the period before both Range("A2") references. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Matt." wrote in message ... Hi all! I've changed my sort code to as follows. Now I'm getting an error that states: The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank. Sheets("Accum").Range("A2").CurrentRegion.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Again, any help greatly appreciated. Worksheet setup still the same. Thanks again for any help. cheers, Matt. "Matt." wrote in message ... Hi all! If it is possible, can somebody give me the syntax for sorting a worksheet without selecting it? The worksheet has a header row (row 1). I would like to sort all the values in the worksheet by column A Ascending. Column A is a Date. There will never be more than 50 rows and 10 columns. This is the code I'm using. Sheets("Accum").CurrentRegion.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom The error I'm getting is the method or object isn't supported. Any help greatly appreciated. Thanks in advance. cheers, Matt. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 VBA Sort without Select
Hi Pikus!
I subbed your routine, and showed the Call statement too, for others who may find it useful. I ran it in my spreadsheet, and it worked very well, but I ended up using Chip's solution. Thanks again, cheers, Matt. Call SortSheet(Sheets("Accum")) Sub SortSheet(ws As Worksheet) Dim x As Integer Dim alph As Variant Dim a As Integer Dim y As Integer Dim z As Integer For x = 2 To 50 alph = ws.Cells(x, 1).Value a = x + 1 z = x For y = a To 600 If ws.Cells(y, 1).Value < alph And ws.Cells(y, 1).Value < "" Then alph = ws.Cells(y, 1).Value z = y End If Next y If z < x Then ws.Rows(z).Cut ws.Rows(x).Insert End If Next x End Sub "pikus " wrote in message ... Dim ws As Worksheet See if this helps. I'll be glad to explain how it works if you need me to. - Pikus Set ws = Worksheets(1) For x = 2 To 50 alph = ws.Cells(x, 1).Value a = x + 1 z = x For y = a To 600 If ws.Cells(y, 1).Value < alph And ws.Cells(y, 1).Value < "" Then alph = ws.Cells(y, 1).Value z = y End If Next y If z < x Then ws.Rows(z).Cut ws.Rows(x).Insert End If Next x --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 VBA Sort without Select
I've personally been very happy with this method. If it is slowing yo
down I'd ask if you're using "Application.ScreenUpdating = False" o not. If not, put that at the beginning of your code an "Application.ScreenUpdating = True" at the end. It makes a WORLD o difference. I've used it to sort hundreds of records and it's bee quite fast, so unless you're sorting WAY more records than I am, I'd a least recommend giving it a try. - Piku -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with sort in Excel 2000 | Excel Discussion (Misc queries) | |||
In Excel 2000, How do you select the whole of a worksheet (Select. | Excel Discussion (Misc queries) | |||
In Excel 2000, How do you select the whole of a worksheet (Select. | Excel Discussion (Misc queries) | |||
Excel 2000 Find without select VBA | Excel Programming | |||
Excel 2000 VBA: select filled cells for printing | Excel Programming |