[SOLVED] How to code this macro?

Question

Referring to following online file, I would like to know on how to sort data from column B (original) into column D (Case 2) format using macro

Blank space affects the sorting order, is there any approach to solve this issue?

Range("C3:C8").Select  
Selection.Sort Key1:=Range("C3"), Order1:=xlAscending, Header:=xlGuess, \_  
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod \_  
    :=xlStroke, DataOption1:=xlSortNormal  

Does anyone have any suggestions?
Thanks in advance

Online file: https://1drv.ms/x/s!AuvJV0hx3xTGiQ1whF9SDpIxuKO_?e=lcc9P4

Answer 1

I use another approach to sort those data, problem is solved.

Thank you very much for suggestions (^v^)

Answer 2

In the code that I shared, I copied first the value column B to D so that it will remove some formatting and make sure that the blank cell is really empty.

It seems that the column B in your current sheet have some formatting that needs to be reset.

Answer 3

When I try your given code on new worksheet, it works fine, but it does not work on my existing worksheet.

Sub sortcase3()
Range("B4:B1300").Select
Selection.Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlStroke, DataOption1:=xlSortNormal
Application.CutCopyMode = False
Range("B3").Select
End Sub

Is there anything wrong with this workbook?

Do you have any suggestions?

Thank you very much for any suggestions (^v^)

Online file : https://1drv.ms/x/s!AuvJV0hx3xTGiQ6VzSTX_Nvz91oM?e=ePuHU3

Answer 4

Hi oemSoft, hope you're doing well. I’m Ian, and I’m happy to help you today.

Sub sortcase2()
    Range("D3:D8").Value = Range("B3:B8").Value
    Range("D3:D8").Select
    Selection.Sort Key1:=Range("D3"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
    :=xlStroke, DataOption1:=xlSortNormal
End Sub

This is a user-to-user support forum and I am a fellow user.

I hope this helps, but please let me know if you need anything else.