98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Macro - Code for selecting current range
Macro - Code for selecting current range
Resolved · 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 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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Editing Formulas in Excel CellsAlthough people like to edit a formula in the Formula bar, you can also edit a formula in the cell. |