Filter grid1 with all last records

[wilder1926]

Hello all

I need you help on this code please.

What i need to do is to be able to filter my grid based on 2 criteria.

In column 4 of every rows, i have ID for stores(Ex: 00167D)
In column 5 or every rows, i have the date that the ID was entered in the list with a new cost in column 6.

I will have in this list many times the same ID but with different dates and i will found over 700 different ID also.

So yes, this is a huge data base,.


Now i need to click on a button an just keep the most recent date for every ID in column 4, and delete all the others.

Can you help me with that please?

This is what i have for now but I'm having a runtime error '381' subscript out of range:

strCol4 = MSHFlexGrid1.TextMatrix(r, 4)
strCol5 = MSHFlexGrid1.TextMatrix(r, 5)



Thanks again.

Private Sub FetchNoRowCol(ws As Excel.Worksheet, ByRef NoOfRows As Long, _
        ByRef NoOfColumns As Long)
   
    NoOfRows = ActiveSheet.Cells.SpecialCells(11).Row
NoOfColumns = ActiveSheet.Cells.SpecialCells(11).Column
               
End Sub

and

Dim xlObject     As Excel.Application
Dim xlWB         As Excel.Workbook
Dim NoOfRows     As Long
Dim NoOfColumns  As Long
Dim r As Long, NewRow As Long, RowToDelete As Long
Dim strCol4 As String, strCol5 As String
Dim strNewCol4 As String, strNewCol5 As String



For r = 0 To MSHFlexGrid1.Rows - 1
      If r <= MSHFlexGrid1.Rows - 1 Then
         strCol4 = MSHFlexGrid1.TextMatrix(r, 4)
         strCol5 = MSHFlexGrid1.TextMatrix(r, 5)
         If Len(strCol4) > 0 And Len(strCol5) > 0 Then
            strCol5 = CDate(MSHFlexGrid1.TextMatrix(r, 5))
            For NewRow = r - 1 To MSHFlexGrid1.Rows - 1
               RowToDelete = 0
               If NewRow <= MSHFlexGrid1.Rows - 1 Then
                  If MSHFlexGrid1.TextMatrix(NewRow, 4) = strCol4 Then
                     strNewCol5 = CDate(MSHFlexGrid1.TextMatrix(NewRow, 5))
                     If DateDiff("s", strCol5, strNewCol5) > 0 Then
                        RowToDelete = r
                     ElseIf DateDiff("s", strCol5, strNewCol5) < 0 Then
                        RowToDelete = NewRow
                     End If
                     If RowToDelete Then
                        MSHFlexGrid1.RemoveItem RowToDelete
                        r = r - 1
                     End If
                  End If
               End If
            Next NewRow
         End If
      End If
   Next r