overflow error in range.find

[werafa]

Hi all,
I'm getting an overflow error in the following code. can anyone spot the problem?
myMonth = "November" in this particular example


'find monthname anywhere in usedrange & delete row
Set myCell = myRange.Find(What:=myMonth, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

the full code is:

Sub DeleteMonthData(myMonth As String)Dim mySheet As Worksheet
Dim myRange As Range
Dim myCell As Range
Dim myLoop As Long
Dim mySheets() As Variant


mySheets = myDataSheets 'a string array
For myLoop = 0 To UBound(mySheets)
Set mySheet = ThisWorkbook.Worksheets(mySheets(myLoop))
With mySheet
.Activate
ActiveSheet.UsedRange 'resets the used range
ActiveWindow.View = xlNormalView 'speed up
.DisplayPageBreaks = False 'speed up

Set myRange = .UsedRange
Do
'find monthname anywhere in usedrange & delete row
Set myCell = myRange.Find(What:=myMonth, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If myCell Is Nothing Then 'no more matches found
Exit Do
Else
myCell.EntireRow.Delete
End If
Loop
End With
Next myLoop

'update admin sheet
ThisWorkbook.Worksheets("Admin").Range("E" & GetMonthRow(myMonth)).Value = "Deleted"
ThisWorkbook.Worksheets("Admin").Activate

End Sub

thanks for your help

[werafa]

some more digging:

If I remove all the optional parameters in the find statement (and accept defaults), it works.
if I add the 'after' parameter, it fails. does anyone know why this is so?

I've used Ron DeBruins method from https://www.rondebruin.nl/win/winfiles/MoreDeleteCode.txt

Thanks

[SamT]

:dunno:
mySheet.Cells(.Cells.Count)
is outside of
myRange

Note: Depending on how myDataSheets was created, mySheets' LBound might not be 0



I don't see anything that would cause a Memory overflow condition

[werafa]

well spotted on the mysheet. will test this and post back the result

[werafa]

this was the error - mysheet.cells.count could not be resolved against myrange.cells

Thanks SamT - I would not have spotted that one.

[werafa]

working code is:

Sub DeleteMonthData(myMonth As String)Dim mySheet As Worksheet
Dim myRange As Range
Dim myCell As Range
Dim myLoop As Long
Dim mySheets() As Variant


mySheets = myDataSheets 'a string array
For myLoop = 0 To UBound(mySheets)
Set mySheet = ThisWorkbook.Worksheets(mySheets(myLoop))
With mySheet
.Activate
ActiveSheet.UsedRange 'resets the used range
ActiveWindow.View = xlNormalView 'speed up
.DisplayPageBreaks = False 'speed up

Set myRange = .UsedRange
With myRange
Do
'find monthname anywhere in usedrange & delete row
Set myCell = .Find(What:=myMonth, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If myCell Is Nothing Then 'no more matches found
Exit Do
Else
myCell.EntireRow.Delete
End If
Loop
End With
End With
Next myLoop

'update admin sheet
ThisWorkbook.Worksheets("Admin").Range("E" & GetMonthRow(myMonth)).Value = "Deleted"
ThisWorkbook.Worksheets("Admin").Activate

End Sub

it provides a way to identify cell contents and delete a row if a match is found without using a loop

[Aflatoon]

FYI, the overflow was caused by Cells.Count. With the big grid, you need to use Cells.CountLarge instead.

[werafa]

thanks