Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 21st, 2011, 05:52 PM
Authorized User
 
Join Date: May 2009
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default Use a macro to sort a user selected range

I would like to have the user select a group of data (full rows). (Some number of lines that will be different each time). Then push a button to sort the rows. I recorded a macro first, thinking that I could then adjust the code as necessary.

My problem is that the recorded macro gives the specific range of rows to sort. I want it to sort whatever set of rows the use may have selected at the time the macro is run.

The code I ended up with is:

'ActiveWorkbook.Worksheets("Pugh").Sort.SortFields .Clear
' ActiveWorkbook.Worksheets("Pugh").Sort.SortFields. Add 'Key:=Range("AJ28:AJ33") _
' , SortOn:=xlSortOnValues, Order:=xlDescending, 'DataOption:=xlSortNormal
' With ActiveWorkbook.Worksheets("Pugh").Sort
' .SetRange Range("A28:CN33")
' .Header = xlGuess
' .MatchCase = False
' .Orientation = xlTopToBottom
' .SortMethod = xlPinYin
' .Apply
' End With

The SetRange line is the one I don't know what to do with. How can I run this macro and tell it to just use whatever rows are selected on screen at that moment?
 
Old April 24th, 2011, 01:15 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

Code:
Public Sub sSortSelection()

'use the keyword "Selection" for the currently selected range
With ActiveSheet.Sort
    .SortFields.Clear
    'the key you want to use is the column to sort on. I used column 1, which is "A", column "B" is 2, etc
    .SortFields.Add Key:=Selection.Columns(1), Order:=xlDescending
    .SetRange Selection
    .Apply
End With

End Sub

Last edited by mtranchi; April 29th, 2011 at 03:41 AM..
 
Old April 25th, 2011, 08:47 PM
Authorized User
 
Join Date: May 2009
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you very much.

Clearly, what I did not get was the
.SetRange Selection
statement

Will that work any time I want to perform an action on a range and have selected the range on screen?
 
Old April 26th, 2011, 04:32 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

you're welcome, glad to be of help. yes, as far as i know Selection always refers to whatever is selected on screen. I don't make much use of graphs, pictures, drawings (shapes), so dunno about that, but if a range or a single cell is selected, that's your ticket. :)





Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Macro FIND THIS MONTH in a given range mvatoi Excel VBA 3 August 24th, 2007 08:12 AM
Putting selected range addresses in an array SMI Excel VBA 4 July 7th, 2006 04:49 PM
Excel Formula End Range Changes when Macro Is Run maaron Excel VBA 0 October 11th, 2005 10:56 AM
Excel Formula End Range Changes when Macro Is Run maaron Beginning VB 6 0 October 11th, 2005 10:33 AM
Fire a macro when an option is selected ashu_gupta75 Excel VBA 1 August 12th, 2004 12:42 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.