Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

macro code selecting

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

ResolvedVersion 2003

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

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.

 

Training courses

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Viewing two Excel 2010 Workbooks at the same time

Did you know you can view two Excel 2010 workbooks side by side? Very useful when comparing data without constantly having to go back and forth!

1) Open both Excel workbooks
2) Select Window then select Compare Side by Side with (Spreadsheet 2)
3) When you have finished, select Window again and click Close Side by Side

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.