Sort sheet A-Z advice please

[ipbr21054]

The code in use is supplied below.

The problem i see now & then is column L is out of sync.
Column K in various cells have a colour written in text example BLACK
Column L matches it, So if K30 is BLUE then L30 should also be BLUE

Sometimes when i use this sheet i notice a cell in column L is empty but the cell in column K has say CLEAR in it ????

So info for you.
Headers in row 6
Row 7 is hidden
Range is A6-L6 then down the page

Do you see an issue in the code below please

Private Sub PressToSort_Click()
Dim x As Long
Dim ws As Worksheet
Set ws = Sheets("MCLIST")
Dim SortColumn As String

Select Case ComboBox1

Case "CUSTOMER"
SortColumn = "A"

Case "FRAME NUMBER"
SortColumn = "B"

Case "MAKE"
SortColumn = "C"

Case "MODEL"
SortColumn = "D"

Case "ITEM BOUGHT"
SortColumn = "E"

Case "CHIP"
SortColumn = "F"

Case "COUNTRY"
SortColumn = "G"

Case "ORIGINAL PN"
SortColumn = "H"

Case "YEAR"
SortColumn = "I"

Case "LEAD"
SortColumn = "J"

Case "LEAD TYPE"
SortColumn = "K"

Case "CONNECTOR USED"
SortColumn = "L"

End Select
If Len(SortColumn) <> 0 Then
Application.ScreenUpdating = False
With ws
If .AutoFilterMode Then .AutoFilterMode = False
x = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A7:L" & x).Sort Key1:=.Cells(2, SortColumn), Order1:=xlAscending, Header:=xlGuess
Range("A6").Select
Range("A8").Select
End With
End If
Unload AtoZMotorcycles
End Sub

 

[ipbr21054]

I can only supply that when it happens.

The problem is that column K & L dont always have matching values.

Example K22 BLUE where L22 is something’s empty or say CLEAR as we know it should match & be BLUE

I can’t put formula in column L as some of the values in column K are only in K & not needed in L

If this is troublesome I’m happy to leave it as it sounds like it’s going to be.

 

[Joe4]

Did you try making the changes I suggested at the bottom of my last post?

Other than that, without having access to your stuff or seeing what happens, I don't know how much we can help.

 

[ipbr21054]

At present playing with it all is fine.

 

[ipbr21054]

So its now just done it.
See screenshot

I will upload a file for you

 

[Joe4]

You cannot upload files to this site - you would need to upload it to a file sharing site, and then provide a link to it here in this thread.

What column did you elect to sort by in the example you posted above?

 

[ipbr21054]

I know it was just my wording.

I would really like to keep values the same to try & track issue down BUT not keen to post for just anybody to view

 

[Joe4]

I know it was just my wording.

I would really like to keep values the same to try & track issue down BUT not keen to post for just anybody to view

We recommend on any files you upload or any screen prints you post here to "dummy up the data", i.e. remove any personal or sensitive information first.
Usually the issues can be demonstrated with dummy/made-up data.

 

[ipbr21054]

Sorry but i will leave it as to many rows to make up dummy info.
I think as it is will create the issue & moving all the info from it will only cause it to be more problematic.

Thanks for time so far

 

[NoSparks]

Does clearing sortfields first and only sorting the actual data range make any difference ?

    With ws
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Sort.SortFields.Clear
        .Range("A7:L" & x).Sort Key1:=.Cells(7, SortColumn), Order1:=xlAscending, Header:=xlGuess
        '.Range("A6").Select
        .Range("A8").Select
    End With