|
inoueさんの方法と同じですが、作業列を挿入し数式を入れて一気に番号を付与し、
それをキーにして並べ替える。というコードを組んでみました。
Sub MySort()
Const Municipality As String = _
"{""北海道"",""青森県"",""岩手県"",""宮城県"",""秋田県""," & _
"""山形県"",""福島県"",""茨城県"",""栃木県"",""群馬県"",""埼玉県""," & _
"""新潟県"",""長野県"",""千葉県"",""東京都"",""神奈川県"",""山梨県""," & _
"""富山県"",""石川県"",""福井県"",""岐阜県"",""静岡県"",""愛知県""," & _
"""三重県"",""滋賀県"",""京都府"",""大阪府"",""兵庫県"",""奈良県""," & _
"""和歌山県"",""鳥取県"",""島根県"",""岡山県"",""広島県"",""山口県""," & _
"""徳島県"",""香川県"",""愛媛県"",""高知県"",""福岡県"",""佐賀県""," & _
"""長崎県"",""熊本県"",""大分県"",""宮崎県"",""鹿児島県"",""沖縄県""}"
Application.ScreenUpdating = False
Range("B:B").Insert xlShiftToRight
With Range("A1", Range("A65536").End(xlUp))
.Offset(, 1).Formula = _
"=MATCH(LEFT($A1,((MID($A1,4,1)=""県"")+3))," & _
Municipality & ",0)"
.Resize(, 2).Sort Key1:=Columns(2), Order1:=xlAscending, _
Header:=xlNo, Orientation:=xlSortColumns
End With
Range("B:B").Delete xlShiftToLeft
Application.ScreenUpdating = True
End Sub
|
|