macro code selecting
The UK's Number 1 for Microsoft Office Training Add this page to your favourites/bookmarksBookmark page
 
View printable version of pagePrintable version
Plus One Google
Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Macro - Code for selecting current range

Macro - Code for selecting current range

resolvedResolved · High Priority · Version 2003

replyReply Mon 2 Nov 2009, 09:57Delegate Gareth said...

Gareth has attended:
Excel VBA Intro Intermediate course
Excel Intermediate course
Excel Advanced course

Macro - Code for selecting current range

Hi

Im having difficulty with a macro im trying to build. Each day I copy and past a load of data into excel , roughly 2000 lines. This id done daily and the data changes.

Once the data has been copied and pasted in, I then sort 3 of the columns in ascending order. I have recorded myself doing this and got the following code

Sub sort()
'
' sort Macro
'

'
Range("H23").Select
Range("A22:V2237").sort Key1:=Range("T23"), Order1:=xlAscending, Key2:= _
Range("H23"), Order2:=xlAscending, Key3:=Range("L23"), Order3:= _
xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
xlSortNormal, DataOption3:=xlSortNormal
Range("H21").Select
End Sub



Im looking for a piece of code that will be more flexible and not absolute to the range "A22:V2237". I.E when i go to run the macro the following day and there are more than 2237 lines of data this will be included.


The cells H23 will not change.

Thanks

Gareth

For upcoming training course dates see: Pricing & availability

replyReply Thu 5 Nov 2009, 10:15Trainer Anthony said...

RE: Macro - Code for selecting current range

Hi Gareth, thanks for the query. I've tweaked your code slightly, let me know if it achieves what you want. Note that if you've got any blank rows in your data, the current region will stop there, and possibly distort the results.

Range("H23").CurrentRegion.sort Key1:=Range("T23"), Order1:=xlAscending, Key2:= _
Range("H23"), Order2:=xlAscending, Key3:=Range("L23"), Order3:= _
xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
xlSortNormal, DataOption3:=xlSortNormal
Range("H21").Select

Hope this helps,

Anthony

Wed 11 Nov 2009: Automatically marked as resolved.

 

Please browse our web site to find out more about
excel course in london and other Microsoft training courses.

Excel tip:

Viewing Many Worksheets

If there are more worksheets in your workbook than there is room to show all their tabs at the bottom of the screen - Right click on the navigation arrows. A list of all your worksheets is shown. You just click on the one that you want to access. If you have more than 15 worksheets, select in the list and choose your worksheet from hundreds.

View all Excel hints and tips


Microsoft Certified Partner Accredited Training Provider: Institute of IT Training Institute of Leadership and Management - Certified Courses Security Seal verified by visa, mastercard securecard