werafa
03-02-2018, 04:32 PM
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
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