Delete Entire Row After Finding Name in Array

[soulz9]

Hello,

I am trying to code the following:

I have supplier names in Column A, and I am searching by the name of the supplier by using the match command, which provides the Row value of the searched name.

Then I would like to delete the supplier that has been found in the array.

So I am using the following code to delete the entire row and shift it up.

SearchVal = Me.txtSearch.Value
RowVal = Application.Match(CDbl(SearchVal), ws.Range("B:B"), 0) 

 ws.Cells(RowVal, 1).EntireRow.Select
 Selection.Delete Shift:=xlUp
 MsgBox "Item deleted from database"

The 1 in ws.Cells is the Column where the data is found, which is the whole Column A.

I can find the correct name in the array but once I click delete, this error is showing

Run-time error '1004':

Select method of Range class failed

Thank you, anyone please shed some light, will appreciate your efforts.

 

[circledchicken]

Hi,

Not sure if it will make a difference but just incase it helps - did you already try the method above without Selecting? i.e. replace:

ws.Cells(RowVal, 1).EntireRow.Select
Selection.Delete Shift:=xlUp
MsgBox "Supplier deleted from database"

with

ws.Cells(RowVal, 1).EntireRow.Delete Shift:=xlUp
MsgBox "Item deleted from database"

 

[soulz9]

Re: Delete Entire Row After Finding Name in Array (Resolved)

You just fixed my problem.

Now I can delete without the error. Its working like a charm.

Thank you

Hi,

Not sure if it will make a difference but just incase it helps - did you already try the method above without Selecting? i.e. replace:

ws.Cells(RowVal, 1).EntireRow.Select
Selection.Delete Shift:=xlUp
MsgBox "Supplier deleted from database"

with

ws.Cells(RowVal, 1).EntireRow.Delete Shift:=xlUp
MsgBox "Item deleted from database"

 

[circledchicken]

Great - your welcome. Thanks for the feedback.

 

[BiocideJ]

Hi,

Not sure if it will make a difference but just incase it helps - did you already try the method above without Selecting? i.e. replace:

ws.Cells(RowVal, 1).EntireRow.Select
Selection.Delete Shift:=xlUp
MsgBox "Supplier deleted from database"

with

ws.Cells(RowVal, 1).EntireRow.Delete Shift:=xlUp
MsgBox "Item deleted from database"

Out of curiosity, do you know what would make the second version work when the first doesn't? It seems like if Excel can delete a selection it should be able to Select it too. Very strange.

 

[circledchicken]

Out of curiosity, do you know what would make the second version work when the first doesn't? It seems like if Excel can delete a selection it should be able to Select it too. Very strange.

Hi,

I think if you wanted to use the Select method you need to activate the Sheet first i.e. like this:

[B][COLOR="DarkRed"]ws.Activate[/COLOR][/B]
ws.Cells(RowVal, 1).EntireRow.Select
Selection.Delete Shift:=xlUp
MsgBox "Supplier deleted from database"

I'm not sure of the actual reason the method requires activation though.

 

[BiocideJ]

I think if you wanted to use the Select method you need to activate the Sheet first i.e. like this:

That actually makes perfect sense. Thanks.
Something I'll have to tuck away in my memory for later.