Sort a worksheet

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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,974
Office Version
2013
Platform
Windows
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 ?
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,267
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.
 

Computerman

Board Regular
Joined
Mar 3, 2016
Messages
91
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.
 

Computerman

Board Regular
Joined
Mar 3, 2016
Messages
91
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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,974
Office Version
2013
Platform
Windows
@Computerman...OK, understand that.
Hopefully @AlphaFrog has provided some help.

Maybe the street number is throwing your sort out.
 
Last edited:

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,267
IDK

Can you post a desensitized workbook that exhibits the same issue?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,956
Office Version
365
Platform
Windows
Do you have any rows or columns that are completely blank?
 

Computerman

Board Regular
Joined
Mar 3, 2016
Messages
91
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
 

Computerman

Board Regular
Joined
Mar 3, 2016
Messages
91
***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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,872
Messages
5,489,433
Members
407,689
Latest member
Doublealpha

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top