Delete method of Range class failed - HELP!!!

[richilli]

Hi

Any help on this would be appreciated cos its driving me insane.

I have a function in VB.NET that takes in an excel range and tries to
delete rows where the first column starts with a string. Only it
doesnt work and all i get is "Delete method of Range class failed"
whatever i do.

Any suggestions?

Public Function DeleteRowsContaining(ByVal objRange As
Excel.Range, ByVal strDeleteString As String) As Excel.Range

Dim lngRow As Long
Dim strCellValue As String

For lngRow = objRange.Rows.Count To 1 Step -1
strCellValue = objRange.Cells(lngRow, 1).Value
If strCellValue.StartsWith(strDeleteString) = True Then
objRange.Rows(lngRow).EntireRow.Delete()
End If
Next lngRow

Return objRange

End Function

Many thanks in advance

R

 

[Bernie Deitrick]

R,

I think that the line

objRange.Rows(lngRow).EntireRow.Delete()

should be

objRange.Rows(lngRow).EntireRow.Delete

This may or may not help, but below is a re-write that works entirely within
Excel.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myRange As Range
Set myRange = Range("A1:A15")
Set myRange = DeleteRowsContaining(myRange, "Bernie")
MsgBox myRange.Address
End Sub

Public Function DeleteRowsContaining( _
ByVal objRange As Excel.Range, _
ByVal strDeleteString As String) As Excel.Range

Dim lngRow As Long
Dim strCellValue As String

For lngRow = objRange.Rows.Count To 1 Step -1
strCellValue = objRange.Cells(lngRow, 1).Value
If Left(strCellValue, Len(strDeleteString)) = strDeleteString Then
objRange.Rows(lngRow).EntireRow.Delete
End If
Next lngRow

Set DeleteRowsContaining = objRange

End Function