Run time error on a VBA sort

Montez659

Well-known Member
Joined
May 4, 2005
Messages
918
It's been a while since I have palyed with so please have grace and mercy on me. I am working on a macro and contained in that macro is a way to add departments to a list. When the userform populates, it pulls the departments to a combo box. Before this is done, I have a simple sort run so that the departments are in alphabetical order. I have done this many times before, however this time, unless I am viewing the actual sheet that the data is contained, "Departments", I get run-time error 1004 stating "The sort reference is not valid. Make sure that it is within the data you want to sort, and the first Sort By box isn't the same or blank."
Here is the data that I am using, in Ranges A2 to last on "Departments":
Building Grounds
Fellowship Hall
Lower Parking Lot
Main Worship Center
Sunday School Area
Upper Parking Lot
Youth Area

Here is the sort code:
With Sheets("Departments")
last = .Range("A65536").End(xlUp).Row
.Range("A" & 2, "A" & last).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

btw, everything has changed a bunch since my last time here. Are there no "code" buttons anymore?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Montez659

Well-known Member
Joined
May 4, 2005
Messages
918
That was how I originally had it and I switched it to try it. It has no problem opening as long as I am actually viewing sheet "Departments", its just when I switch to a different sheet. Eventually these will all be hidden. BTW, I am running XL2003
 

Montez659

Well-known Member
Joined
May 4, 2005
Messages
918
I just realized when I was stepping through the Macro that for some reason it is pulling data off of the sheet that I am viewing instead of the "Departments" sheet. I cannot see how or why that is happening.
 
Last edited:

Montez659

Well-known Member
Joined
May 4, 2005
Messages
918
Ok, I figured it out, just for future reference for anyone else. Of course I was using the With statement, and so in the line :

.Range("A" & 2, "A" & last).Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo, _

I was missing the period before Range("A2"), therefore not tying it to the With statement. Thanks for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,307
Messages
5,641,442
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top