Hello,
I am trying to create a macro to sort a column starting at a cell activated by the user. I programmatically select the range of cells starting at the selected cell over which I want to do the sort on. But I am stuck on what to do for the following snippet of code:
Selection.Sort Key1:=.Range(<something>) ...
I don't know what to stick in place of <something> to represent the selected range of cells in the column over which the sort will be done. Keep in mind that whatever gets put in place of <something> has to be done programmatically.
Thanks...
Mike
Macro to sort an Excel column
Hot Topic
- Using VB in Excel 2003
- custom toolbar error
- Word.Application
- Left Joins with Multiple Conditions in VBA
- Activate an addin macro, using refferences from local workbook.
- InputBox() in MS Word form textbox.
- Problem with CWnd::RunModalLoop(DWORD dwFlags) in wincore.cpp
- Excel: Treeview handle events for dynamically added control
- PowerPoint, WORD, and Macros
- ActiveX Control Problem
Microsoft ISV
- What kind of info do you want to see on the MSDN ISV Community Center site?
- Business Scorecard Manager 2005 database problem
- pass parameter to stored procedure
- AppHung in SQL Server (SQLEXPRESS)
- Hyper link Macro
- Can no longer enter wscript for debug
- VBA: Timer event?
- Questions on ISV's and regarding end users
- Can not connet to BSM server
- Extracting email addresses (from column), date and subject line from Outlook to excel
Macro to sort an Excel column
greg aiken
Thanks for the response. Your suggestion worked but with a problem - the data in the preceeding columns became scrambled. However, if the column is preceeded by a column containing no data then preceeding columns with data and before the blank column are not affected by the sort
Mike
Rob McCaughey
Mike,
Use "activecell"
The example below works fine
Regards Fiftyfive
Select a Range or one Cell in a Column or Matrix
Sub SortRange()
Dim MyErr As Label
On Error GoTo MyErr
Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
MyErr:
Select Case Err
Case 0
'Everything is OK
Case Else
MsgBox Err.Description
End Select
Err.Clear
End Sub
rreinman
Mike
Asume: you have a sheet with matrix A1:D10 filled with data
A sort started from a selection on a single cell (e.g "D11") affects to the currentregion ("A1:D11" if united), even when "D11" doesn't have content.
If only D4 has content (e.g. "Test"), the matrix A1:D11 will be sorted.
D12 has no relation with the matrix A1:D10, even when there is content. So Sort will have no effect
You can test 'currentregion' bij Funtion-Key F5, select currentregion
Suc6
FiftyFive
ferchuz
Be careful with sorting in Excel, if you sort only one column and the data in that column is related to a preceding or following column then you could scramble your data.
For example, columns first name and surname, if you sort the surname column then only that column is sorted the first name column is not. Just be careful.
Your Range in this case would be the cell references of the information you want to sort, for example A1:C10.