0

I need to get first row and last row for each read value from an array.
The array is in yellow. From it, I read the column D (currency).
The expected result is in blue: 1st and last row number for each value find in D column from the array.

Does somebody can propose a simple VBA code to achieve it?

Example

GSerg
  • 76,472
  • 17
  • 159
  • 346
JRMBAL
  • 9
  • 2

1 Answers1

1

Try the next code, please:

Sub testFirstLastOccurrences()
 Dim sh As Worksheet, lastRA As Long, lastRD As Long, arrA As Variant, k As Long
 Dim ArrFin As Variant, i As Long, j As Long, lngFirst As Long, lngLast As Long
 
 Set sh = ActiveSheet
 lastRA = sh.Range("A" & Rows.count).End(xlUp).Row
 lastRD = sh.Range("D" & Rows.count).End(xlUp).Row
 
 arrA = sh.Range("A2:A" & lastRA).Value
 ReDim ArrFin(1 To 3, 1 To lastRD)
 For i = 1 To UBound(arrA)
    For j = 2 To lastRD
        If lngFirst = 0 And arrA(i, 1) = sh.Range("D" & j).Value Then lngFirst = j
        If arrA(i, 1) = sh.Range("D" & j).Value Then lngLast = j
    Next j
    If lngFirst <> 0 Then
        k = k + 1
        ArrFin(1, k) = arrA(i, 1): ArrFin(2, k) = lngFirst: ArrFin(3, k) = lngLast
    End If
    lngFirst = 0: lngLast = 0
 Next i
 ReDim Preserve ArrFin(1 To 3, 1 To k)
 sh.Range("F1:H1").Value = Array("ARRAY()", "1st Row", "Last Row")
 'Drop the processed data:
 sh.Range("F2").Resize(UBound(ArrFin, 2), UBound(ArrFin, 1)).Value = _
         WorksheetFunction.Transpose(ArrFin)
End Sub
FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • @JRMBAL: Did you find time to test the above code? – FaneDuru Aug 11 '20 at 17:50
  • Your code is perfectly working. – JRMBAL Aug 31 '20 at 14:59
  • @JRMBAL: Glad I could help! But, we here when somebody answer our question, tick the left code side check box, in order to make it **accepted answer**. In this way, if somebody else will search for a similar issue will know that the code works... – FaneDuru Aug 31 '20 at 16:05