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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,071
Messages
5,835,216
Members
430,347
Latest member
Mayan

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