Everybody.

I built the following table as a sample because I'm trying to do a vba code that can select the rows based in the values from the column "Date". For example, all rows with the value 01/04/2022 should be selected in the end of the code.

I can't use AutoFilter function, because some columns are from a external source, so Excel won't allow me to use it. Other thing is the format I'm using for the date. Here in my country the standard is dd/mm/yyyy.

I tried to solve this problem with the following code:

Sub test()
Dim c As Range, FoundCells As Range
Dim firstaddress As String

Application.ScreenUpdating = False
With Sheets("Test")
    'find first cell that contains what you want to find
    Set c = .Cells.Find(What:="01/04/2022", After:=.Cells(Rows.Count, 1), LookIn:=xlValues, LookAt:= _
    xlPart, MatchCase:=False)
  
    'if the search returns a cell
    If Not c Is Nothing Then
        'note the address of first cell found
        firstaddress = c.Address
        Do
            'FoundCells is the variable that will refer to all of the
            'cells that are returned in the search
            If FoundCells Is Nothing Then
                Set FoundCells = c
            Else
                Set FoundCells = Union(c, FoundCells)
            End If
            'find the next instance of "rec"
            Set c = .Cells.FindNext(c)
        Loop While Not c Is Nothing And firstaddress <> c.Address
              
        'after entire sheet searched, select all found cells
        FoundCells.Select
    Else
        'if no cells were found in search, display msg
        MsgBox "No cells found."
    End If
End With
Application.ScreenUpdating = True
End Sub

But it isn't working properly. It never find the cells. I don't know why..

Could somebody help me, please?

Att,

Paulo

CodePudding user response：

You need to change only one line

Set c = .Cells.Find(What:=#4/1/2022#, After:=.Cells(Rows.Count, 1), LookIn:=xlFormulas, LookAt:= _
    xlPart, MatchCase:=False)

Putting the date into # ...# makes sure that VBA will recognise it as a date and setting LookIn to xlFormulas will make sure Find looks for a date.