Sort sheet A-Z advice please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,953
Office Version
  1. 2007
Platform
  1. Windows
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




VBA Code:
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
 
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.
 
Upvote 0
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.
 
Upvote 0
So its now just done it.
See screenshot

I will upload a file for you

EaseUS_2025_02_26_19_27_33.jpg
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

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