Computerman

Board Regular
Joined
Mar 3, 2016
Messages
91
I have a worksheet with columns A through R. I need to sort this worksheet by the value that is in Column C. I have searched for the answer and have tried several suggestions, but the worksheet never sorts. In my code below I have commented out all the things I have tried. Is what I am trying to do possible, and if so, how?

Code:
Private Sub cmdBuildOrders_Click()
cmdBuildOrders.Caption = "Running"
cmdBuildOrders.BackColor = RGB(255, 255, 0)
cmdBuildOrders.ForeColor = RGB(0, 0, 0)
Application.Wait (Now + TimeValue("0:00:01")) 'allows command button to change text and back color

lastrow = Sheet3.Range("A100").End(xlUp).Row

Sheet3.Activate
'Range("C1:C17", Range("C1").End(xlDown)).Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes

'ActiveSheet.Sort.SortFields.Clear
'Columns("A:R").Sort Key1:=Range("C2:C17"), Order1:=xlAscending, Header:=xlYes

'Range("A2:R17").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes

'Range("C2").CurrentRegion.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes

'LastRowSort = Sheet3.Cells(Rows.Count, 2).End(xlUp).Row
'Range("A2:R" & LastRowSort).Sort key1:=Range("C2:C" & LastRowSort), _
   order1:=xlAscending, Header:=xlNo


'For Row = 2 To LastRow
'    Sheet4.Cells(Row, 2).Value = "MPS"                             'Customer Code Parent Row
'    Sheet4.Cells(Row, 3).Value = "ATNA"                           'Project Code Parent Row
'    Sheet4.Cells(Row, 5).Value = "Standard"                     'Type Parent Row
'    Sheet4.Cells(Row, 6).Value = "Pick Pack and Ship"    'Attribute Parent Row
'    Sheet4.Cells(Row, 13).Value = "False"                         'IsAccessory Child Row
'    Sheet4.Cells(Row, 14).Value = "False"                         'IsConfiguration Child Row
'    Sheet4.Cells(Row, 15).Value = "False"                         'Reservation Parent Row
'
'Next Row
cmdBuildOrders.Caption = "Build Customer Page"
cmdBuildOrders.BackColor = RGB(0, 176, 80)
cmdBuildOrders.ForeColor = RGB(255, 255, 255)
End Sub

Computerman
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Dumb question here....but are you sure Sheet3 is the correct sheet ?
Have you tried activating the sheet by name to be sure?
Can you Upload your file to a sharing site, Dropbox for instance, and then post the link back here ?
 
Upvote 0
Code:
Range("A1:R17").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes

Range("C2").CurrentRegion.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes

These above syntax should work.

What's the nature of the data in column C ?
Can you manually sort using the menu?

It's possible to have formulas that use relative row references that produce the same result order when you sort the formulas. In other words, when you sort the formulas, the formulas recalculate and return the same pre-sorted order.
 
Upvote 0
Michael,
I am absolutely positive that sheet 3 is the correct sheet. I even double checked right now to be sure. I am not comfortable posting my file to any file sharing site as it contains customer sensitive information.
 
Upvote 0
Code:
Range("A1:R17").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes

Range("C2").CurrentRegion.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes

These above syntax should work.

What's the nature of the data in column C ?
Can you manually sort using the menu?

It's possible to have formulas that use relative row references that produce the same result order when you sort the formulas. In other words, when you sort the formulas, the formulas recalculate and return the same pre-sorted order.

AlphaFrog,
I noticed that the range was changed in your line 1 above. I changed my code to match your line. It did not work. The data that is in column C is address information, street number and street name. I can manually sort the data. There are no formula in the spreadsheet, it is a direct data pull from a sharepoint page.
Computerman
 
Upvote 0
@Computerman...OK, understand that.
Hopefully @AlphaFrog has provided some help.

Maybe the street number is throwing your sort out.
 
Last edited:
Upvote 0
Do you have any rows or columns that are completely blank?
 
Upvote 0
Fluff,
while there are no Rows that are completely bank, There is one column that is completely empty. I will test taking that column out and see what happens

Computerman
 
Upvote 0
***SOLVED***
Fluff, and all who made suggestions,
Thank you all for your suggestions. Following Fluff’s suggestion I removed a blank column from the spreadsheet and using AlphaFrog’s suggestion of Range(A1:Q17)… my spreadsheet is now sorting correctly.
Again, thank you all for your assistance it is appreciated
Computerman
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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
Back
Top