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