|
||||||||||||||||||||
|
|||||||||||||||||||||
|
|||||||
| Notices |
 |
|
|
Thread Tools | Search this Thread | Display Modes |
|
#1
07-12-2008, 07:39 PM
|
|||
|
|||
|
Excel 2007 - Named range disappears in sort macro
I have several named ranges which are sorted individually. These ranges change, ie rows are added and deleted, hence the need for named ranges. Have been using this extensively in 2003 without any issue.
Now I've recorded a macro in 2007 to sort ranges, however within the actual macro, though it starts with Goto Reference:="dataA" (dataA = named range), the actual sort refers to columns & rows, ie B4:F70. The range in the example above has grown to B4:B95, but is only being sorted up to row 70 as per the macro. What can I do to keep the named range as the sort range within the macro? Bungaree 
|
|
#3
07-12-2008, 09:50 PM
|
|||
|
|||
|
SHG,
I made the earlier one up, as the situation of several ranges is actually at work and I'm at home. I discovered the 2007 problem in the macro below. I must have recorded it when there were 240 records and it was only sorting 240 rows though I had inserted about 50 more - I've solved it for this particular worksheet by making the range far larger than I'll ever need - a quick (ugly) fix for this one. I do have other situations where the range needs to be flexible and would like to know why 2007 is causing probs with this. Thanks in anticipation! Bungaree Code:
Application.Goto Reference:="sort_stocklist"
ActiveWorkbook.Worksheets("Prospects").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Prospects").Sort.SortFields.Add Key:=Range( _
"i4:i2473"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Prospects").Sort.SortFields.Add Key:=Range( _
"a4:a2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Prospects").Sort
.SetRange Range("A3:N2000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
Last edited by royUK; 07-13-2008 at 04:15 AM.
|
|
#4
07-13-2008, 04:13 AM
|
||||
|
||||
|
bungaree
You need to read the Forum Rules & use Code Tags when posting code. I will add them this time, but next ime you my find your post closed.
__________________
Hope that helps. RoyUK -------- For Excel consulting, free examples and tutorials visit my site Check out the free Excel Toolbar New members please read & follow the Forum Rules Where to copy the code to Code Tags: Make your code easier for us to read
|
|
| Bookmarks |
New topics in Excel 2007 Help
|
|
|
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
