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

resolvedResolved · High Priority · Version 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:

Editing Formulas in Excel Cells

Although people like to edit a formula in the Formula bar, you can also edit a formula in the cell.

To do this select the cell and press "F2". This puts Excel into Edit mode, and you can move around in the cell and make any necessary changes.

Double-clicking the cell also puts Excel in Edit mode.

View all Excel hints and tips


Server loaded in 0.07 secs.