Problem with delete method of range object in excel macro

[ALSav]

Hi,

I am trying to search a column of a spreadsheet for a value that I have entered in a text box on a form. If any of the cells within my range contains this value I need to delete the entire row. I made many attempts but each time I get a message

Run time error
Delete method of range class failed.

The worksheet I have referenced is correct and I have run various checks to ensure that the correct RowNo is being picked up, yet still the delete method (or the ClearContents method) isn't working. Can anyone see where I am going wrong with this code. I have posted two of my attempts below.

Any help appreciated



Private Sub DeleteID()

Dim cell As Range
Dim search As Range
Dim projectID As String
Dim Selection As Range
Dim RowNo As Integer

projectID = TextBox6.Text

Set Selection = Worksheets(4).Range("A2:A300&quot'A1 is header

For Each cell In Selection
If cell.Text = projectID Then
RowNo = cell.Row
Set search = Worksheets(4).Range("A" & RowNo)
search.EntireRow.Delete
End If
Next cell

End Sub

Or

Private Sub DeleteID()

Dim cell As Range
Dim projectID As String
Dim Selection As Range


projectID = TextBox6.Text

Set Selection = Worksheets(4).Range("A2:A300&quot

For Each cell In Selection
If cell.Text = projectID Then
cell.EntireRow.Delete
End If
Next cell

End Sub

 

[ide]

All of the two examples work at me. I don't know what the trouble is, perhaps try to change the "Selection" variable name to anything else (f.e.: "MySelection", because of existing Selection property).

 

[ALSav]

Thanks for the idea
Only partially worked though. I'm not getting the error message now but it's not deleting anything either.

Also tried this adapted from the microsoft site but ran into the error again

Dim rng As Range
Dim i As Integer, counter As Integer, projectID As String

projectID = TextBox6.Text

'Set the range to evaluate to rng.
Set rng = Worksheets(4).Range("A1:A300&quot

'initialize i to 1
i = 1

'Loop for a count of 1 to the number of rows
'in the range that you want to evaluate.
For counter = 1 To rng.Rows.Count

'If cell i in the range contains an projectID,
'delete the row.
'Else increment i
If rng.Cells(i) = projectID Then
rng.Cells(i).EntireRow.Delete
Else
i = i + 1
End If

Next

 

[ide]

you didn't get the error because you didn't changed all the variable name, i presume (in this case the "Selection" is the range in the specified sheet currently selected)

- could you try your macro on an other pc?
- perhaps record a new macro ('Tools/Macro/Create New Macro' menu), go a worksheet, select a row and delete it. Stop recording, and try to start macro. If it works use the recorded macro commands in your function.

i hope it will work

 

[ALSav]

I'll give it a go, thanks for your help

 

[logius]

Try this code instead.

Dim CellItem as Object

For Each CellItem In ActiveSheet.Range("A1:A300")
   If CellItem = projectID Then
      Range(CellItem, CellItem.End(xlToRight)).Delete
   End If
Next CellItem

Just place the

Dim

statement at the top of the macro and replace your entire

For

loop with this code. As a note, when you delete a row, Excel will automatically move all rows below the deleted row up one unit. If you want to keep the removed row blank, then replace the

.Delete

with

.Clear

. ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com