| |
B列の値を並べ替えるとして、以下のようなコードで C列に行番号を返すことが
できます。
Sub Test_GetRow()
Dim i As Long
Dim MyR As Range, C As Range
Dim FR As Range, D As Range
Application.ScreenUpdating = False
Range("B1", Range("B65536").End(xlUp)).Sort Key1:=Columns(2), _
Order1:=xlAscending, Header:=xlNo, Orientation:=xlSortColumns
For i = Range("B65536").End(xlUp).Row To 2 Step -1
If Cells(i, 2).Value <> Cells(i - 1, 2).Value Then
Cells(i, 2).Insert xlShiftDown
End If
Next i
Set MyR = Range("B:B").SpecialCells(2)
For Each C In MyR.Areas
If C.Count = 1 Then
C.Offset(, 1).Value = _
Application.Match(C.Value, Range("A:A"), 0)
Else
Set FR = Range("A:A") _
.Find(C.Cells(1).Value, , xlValues, , , xlPrevious)
For Each D In C.Offset(, 1)
Set FR = Range("A:A").FindNext(FR)
D.Value = FR.Row
Next
Set FR = Nothing
End If
Next
Set MyR = Nothing
Intersect(Range("B1", Range("B65536").End(xlUp)).SpecialCells(4) _
.EntireRow, Range("B:C")).Delete xlShiftUp
Application.ScreenUpdating = True
End Sub
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
