Hi, I am trying to search a colu

[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

 

[Mossoft]

I do not get that error message when I run either version of your code in XL2000.
However the code is flawed: If two successive rows have projectID in them, only the first is deleted as the rows following a deletion are moved up and the second of the two rows now occupies the row where the first was deleted. The loop continues on the row after.
I would recommend that you do not use "Selection" as the name of your range object as this is a standard excel object.

Good Luck.

M

 

[logius]

I think Mossoft is right, try changing the name Selection to something else. Maybe MySelection or something would be better. Also, Cell is a reserved word in VBA, so don't use it! Use something else to describe it. If it's still messing up on you, try using the VBA debugger.

In the editor, there's a narrow, gray column on the left side of the code area. In here, click once on the lines that the

For

and

Next

statements are on. You should see a red dot appear next to the code and the whole line should be highlighted (it won't work for Dim statments and it HAS to be a line of code). Once you do this, run the macro manually by clicking the "Play" button at the top of the screen. The macro will run until it hits the particular line that you highlighted. If you move the mouse cursor over the variable in the macro, you can see their values. If you move over a Range variable, you shouldn't see anything if it's set properly. If you see something like

Selection = Nothing

then you know something is wrong. To continue with the macro execution, click the Play button again or hit F8.

You can further test it by adding in (I'm talking about the second DeleteID function) a

CellItem.Select

right before the

If

statement. This way, you can switch back to the Excel sheet and see which row is currently being processed. If you get bored of waiting to see 300 lines being processed, you can remove the stops by clicking them again. ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[logius]

By the way, if you're still having problems, email me your workbook, I'll be happy to help you out. I know what a pain dealing with macros can be. ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com