Sort code advice please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
Hi,
I have the code below which im trying to edit but getting a little stuck.

This was the original code which worked fine,columns A-F would sort A-Z & also column G of which was DATE would sort OLD DATE - NEW DATE
Rich (BB code):
Private Sub CommandButton1_Click()

Dim x As Long

Dim ws As Worksheet

Set ws = Sheets("HONDA LIST")

Dim SortColumn As String





Select Case ComboBox1

Case "VIN NUMBER"

SortColumn = "A"



Case "VEHICLE"

SortColumn = "B"



Case "CUSTOMER"

SortColumn = "C"



Case "YEAR"

SortColumn = "D"



Case "HONDA NUMBER"

SortColumn = "E"



Case "SUPPLIED"

SortColumn = "F"



Case "DATE"

SortColumn = "G"



End Select



If ComboBox1 = "VIN NUMBER" Then

Range("A4").Select



ElseIf ComboBox1 = "VEHICLE" Then

Range("B4").Select



ElseIf ComboBox1 = "CUSTOMER" Then

Range("C4").Select



ElseIf ComboBox1 = "YEAR" Then

Range("D4").Select



ElseIf ComboBox1 = "HONDA NUMBER" Then

Range("E4").Select



ElseIf ComboBox1 = "SUPPLIED" Then

Range("F4").Select



ElseIf ComboBox1 = "DATE" Then

Range("G4").Select

End If



If Len(SortColumn) <> 0 Then

Application.ScreenUpdating = False

End If



With ws

If .AutoFilterMode Then .AutoFilterMode = False

x = .Cells(.Rows.Count, 1).End(xlUp).Row

.Range("A3:G" & x).Sort Key1:=.Cells(2, SortColumn), Order1:=xlAscending, Header:=xlGuess

End With



Unload Me

End Sub


I would like for the DATE to sort NEW - OLD
Ive tried to edit the above but running into a few run time errors.

Rich (BB code):
Private Sub CommandButton1_Click()
Dim x As Long
Dim ws As Worksheet
Set ws = Sheets("HONDA LIST")
Dim SortColumn As String


Select Case ComboBox1
Case "VIN NUMBER"
SortColumn = "A"

Case "VEHICLE"
SortColumn = "B"

Case "CUSTOMER"
SortColumn = "C"

Case "YEAR"
SortColumn = "D"

Case "HONDA NUMBER"
SortColumn = "E"

Case "SUPPLIED"
SortColumn = "F"

End Select

If ComboBox1 = "VIN NUMBER" Then
    Range("A4").Select

ElseIf ComboBox1 = "VEHICLE" Then
    Range("B4").Select

ElseIf ComboBox1 = "CUSTOMER" Then
    Range("C4").Select

ElseIf ComboBox1 = "YEAR" Then
    Range("D4").Select

ElseIf ComboBox1 = "HONDA NUMBER" Then
    Range("E4").Select

ElseIf ComboBox1 = "SUPPLIED" Then
    Range("F4").Select
End If

If Len(SortColumn) <> 0 Then
Application.ScreenUpdating = False
End If

With ws
If .AutoFilterMode Then .AutoFilterMode = False
x = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A3:G" & x).Sort Key1:=.Cells(2, SortColumn), Order1:=xlAscending, Header:=xlGuess

    End With
    
Select Case ComboBox1
Case "DATE"
SortColumn = "G"

End Select
If ComboBox1 = "DATE" Then
    Range("G4").Select
    
If Len(SortColumn) <> 0 Then
Application.ScreenUpdating = False
End If

With ws
If .AutoFilterMode Then .AutoFilterMode = False
x = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A3:G" & x).Sort Key1:=.Cells(2, SortColumn), Order1:=xlDecending, Header:=xlGuess
    End With
    End If
    Unload Me
    End Sub
 
Run fluff's code again after correcting the descending spelling mistake.
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Yep now seen that and changed it.

The codes now sorts ALL columns Z-A

Column A,B,C,D,E,F need to be A-Z
Column G needs to be LATEST DATE - OLD DATE

This is starting from Row 4 down
 
Upvote 0
Then change your range in the Descending line
 
Upvote 0
Sorry i mean row 3 down but im trying hard to explain that the order i should see starts at row 3 then down the page.
Column A-F should be A-Z where G should be LATEST-OLDEST
 
Upvote 0
All the other columns are sorted A-Z previously in your code so you have the code for that so you just need to use that line again and then the descending line with the range adjusted so it just applies to column G.
 
Upvote 0
Im expecting the Red code to sort columns A-F in the order of A-Z starting at Row 3 then down the page.

Then the Blue code to sort column G LATEST - OLDEST date starting at Row 3 theb down the page.

What i see happen is all coloumns start at row 3 & down the page BUT Z-A & OLD - LATEST & NOT A-Z and LATEST - OLDEST

Rich (BB code):
Private Sub CommandButton1_Click()
Dim x As Long
Dim ws As Worksheet
Set ws = Sheets("HONDA LIST")
Dim SortColumn As String


Select Case ComboBox1
Case "VIN NUMBER"
SortColumn = "A"

Case "VEHICLE"
SortColumn = "B"

Case "CUSTOMER"
SortColumn = "C"

Case "YEAR"
SortColumn = "D"

Case "HONDA NUMBER"
SortColumn = "E"

Case "SUPPLIED"
SortColumn = "F"

End Select

If ComboBox1 = "VIN NUMBER" Then
    Range("A4").Select

ElseIf ComboBox1 = "VEHICLE" Then
    Range("B4").Select

ElseIf ComboBox1 = "CUSTOMER" Then
    Range("C4").Select

ElseIf ComboBox1 = "YEAR" Then
    Range("D4").Select

ElseIf ComboBox1 = "HONDA NUMBER" Then
    Range("E4").Select

ElseIf ComboBox1 = "SUPPLIED" Then
    Range("F4").Select
End If

If Len(SortColumn) <> 0 Then
   Application.ScreenUpdating = False
   
   With ws
   If .AutoFilterMode Then .AutoFilterMode = False
   x = .Cells(.Rows.Count, 1).End(xlUp).Row
   .Range("A3:G" & x).Sort Key1:=.Cells(2, SortColumn), Order1:=xlAscending, Header:=xlGuess
   
       End With
End If
Select Case ComboBox1
Case "DATE"
SortColumn = "G"

End Select
If ComboBox1 = "DATE" Then
    Range("G4").Select
End If
If Len(SortColumn) <> 0 Then
   Application.ScreenUpdating = False
   
   With ws
   If .AutoFilterMode Then .AutoFilterMode = False
   x = .Cells(.Rows.Count, 1).End(xlUp).Row
   .Range("A3:G" & x).Sort Key1:=.Cells(2, SortColumn), Order1:=xlDescending, Header:=xlGuess
       End With
End If
    Unload Me
    End Sub
All the other columns are sorted A-Z previously in your code so you have the code for that so you just need to use that line again and then the descending line with the range adjusted so it just applies to column G.


I understand that BUT thts what i thought i did when i started this post
 
Upvote 0
@Fluff, the spelling mistake in xlDecending still needs correcting.

@ipbr21054 as I have stated change the range on the code in blue
VBA Code:
.Range("A3:G" & x)
to
Rich (BB code):
.Range("G3:G" & x)
 
Last edited:
Upvote 0
Using this
Rich (BB code):
 .Range("G3:G" & x).Sort Key1:=.Cells(2, SortColumn), Order1:=xlDescending, Header:=xlGuess

Gives me sort range invalid
 
Upvote 0
Column G just sorted fine for me with DATE in the ComboBox using that code, you did just alter the blue code (though I would change the 2 to a 3)?
 
Upvote 0
Still the same,see screenshot
 

Attachments

  • 533.jpg
    533.jpg
    94.3 KB · Views: 4
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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