Find Range of Rows to Delete using FIND

[Brian Feth]

Windows 11, Excel 2021: I am trying to fine the row numbers of the top date in the column (easy) then the first occurance of a chosen date ("11/25/2024") working from the bottom of the column up. I'm trying to use FIND to identify the row number of that date ("11/25/2024"). Can't get it to work. I keep geting the error message "Object Variable or Width Block Variable not set" at the FIND command. You can see I tried a couple of ways to enter the variables into the FIND command. I very much appreciate any help you van give.

Sub FindDateRange()

Dim TopDate_Row     As Integer
'Dim BottomDate_Row  As Integer
Dim BottomDate_Row  As Variant
Dim TargetDate      As Variant

' Find Range of Rows to Delete using FIND . Finds top row (TopRow_Date)then, Finds first occurance of a date ( (bottom up).
  
   TargetDate = Range("Q4").Value '11/25/2024
  
    Range("C5").Select
    Selection.End(xlDown).Select
    TopDate_Row = Selection.Row 'Row of top Date in Column
   
    Selection.End(xlDown).Offset(1, 0).Select 'goes to "C591", aka bottom of Column range
     
    'BottomDate_Row = ActiveSheet.Columns("C:C").Find(What:=TargetDate, After:=ActiveCell, LookIn:=xlValues, _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Row
   
    BottomDate_Row = ActiveSheet.Columns("C:C").Find(What:="11/25/2024", After:=[C591], LookIn:=xlValues, _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Row
   
    ' Error message "Object Variable or Width Block Variable not set"
End Sub

 

[Alex Blakenburg]

What version of Excel are you using, please update your profile to show the version as the solution often depends on the version ?
Do you and anyone using the macro have access to XMatch ?
If you do the use the worksheet function XMatch in your code, it is far more forgiving on date format.
Find xlValues is the least forgiving on date formats, if you don't have formulas generating the date xlFormulas is more forgiving.

 

[Skyybot]

The Find function returns a Range Object and therefore has to be set

Set fnd = Range.Find("Search_String")

get the row by

fndRow = fnd.Row

 

[Brian Feth]

What version of Excel are you using, please update your profile to show the version as the solution often depends on the version ?
Do you and anyone using the macro have access to XMatch ?
If you do the use the worksheet function XMatch in your code, it is far more forgiving on date format.
Find xlValues is the least forgiving on date formats, if you don't have formulas generating the date xlFormulas is more forgiving.

Alex, thank you for your reply. I have Excel 2021 which includes XMatch. I can see immediately how that should do what I need. Thank you again!

 

[Brian Feth]

The Find function returns a Range Object and therefore has to be set

Set fnd = Range.Find("Search_String")

get the row by

fndRow = fnd.Row

Thank you for your reply Skyybot. I'll have to do a bit of studing on those issues.

 

[Alex Blakenburg]

See if this gives you enough to go by. To get the first occurrence instead of the last occurrence change the -1 to 1 or simply leave it out since it's the default.
Using .Value2 means it picks up the numeric representation of the date eg your 25 Nov 2024 will be 45621, this minimises the risk of date formatting issues.
My iferror is not the most common approach but it lets me define the row as long and test for 0 as the not found value.
Since Match gives the position in the range and you want the row number, make sure you start the range from row 1.

Sub FindDateRange()

    Dim TopDate_Row As Long
    Dim BottomDate_Row As Long
    Dim lngTargetDate As Long
    Dim rngDate As Range
    
    ' Find Range of Rows to Delete using FIND . Finds top row (TopRow_Date)then, Finds first occurance of a date ( (bottom up).
    
    lngTargetDate = Range("Q4").Value2 '11/25/2024
    Set rngDate = Range("C1", Cells(Rows.Count, "C").End(xlUp))
        
    With Application
        BottomDate_Row = .IfError(.XMatch(lngTargetDate, rngDate, 0, -1), 0)
    End With
    
    If BottomDate_Row = 0 Then
        MsgBox "Date not found"
        Exit Sub
    End If

End Sub

 

[MARK858]

The Find function returns a Range Object and therefore has to be set

It doesn't need to be set in the OP's code because the .Row at the end of

BottomDate_Row = ActiveSheet.Columns("C:C").Find(What:="11/25/2024", After:=[C591], LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Row

means BottomDate_Row is not referrencing a range, it is referencing a Long Integer

 

[Peter_SSs]

I have Excel 2021

As Alex asked you above ..
Please update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[Skyybot]

It doesn't need to be set in the OP's code because the .Row at the end of

means BottomDate_Row is not referrencing a range, it is referencing a Long Integer

Dang!! I didn't even see that. Wouldn't even look for it. Chalk that up to different programming styles. I try not to use Variant unless I have to. I always Dim a Find variable as Range since that is what it natively returns.