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.

 

[BiocideJ]

Re: Delete Entier Row After Finding Name in Array

I think you need to add an object between the Application and Match.
Works fine for me when this is added.

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


BTW, welcome to the forums.

 

[soulz9]

Re: Delete Entier Row After Finding Name in Array

I think you need to add an object between the Application and Match.
Works fine for me when this is added.

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


BTW, welcome to the forums.

Thanks, but your solution does not change.

The RowVal command is working as it should. It is saving the row value of the name I search for in RowVal.

But the problem is in deleting the entire row.

 

[BiocideJ]

Re: Delete Entier Row After Finding Name in Array

I am making an assumption that ws is the active sheet.

If it is not, you may need to add the code ws.Activate before trying to select (and then delete) a cell on that sheet.

Other than that, I have no idea what could be causing your problem.

 

[circledchicken]

Re: Delete Entier Row After Finding Name in Array

Hi,

Perhaps try:

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

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

You might also want to do something if the SearchVal is not found (rather than return an error)? If so, see here for a good example of how to do that:
http://www.cpearson.com/excel/callingworksheetfunctionsinvba.aspx

 

[tigeravatar]

Re: Delete Entier Row After Finding Name in Array

soulz9,

The error you mentioned, "Select method of Range class failed", can sometimes occur when you try to select a locked cell in a protected worksheet. If your database worksheet is protected, you'll likely have to unprotect it before you can delete rows from it

 

[soulz9]

Re: Delete Entier Row After Finding Name in Array

Hi,

Perhaps try:

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

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

You might also want to do something if the SearchVal is not found (rather than return an error)? If so, see here for a good example of how to do that:
http://www.cpearson.com/excel/callingworksheetfunctionsinvba.aspx

Thank you for the comment. I already have the error check mechanism.

If the search button was pressed and the text field was empty, the If statement in the later part of the code will show an error message material number not found.

 

[soulz9]

Re: Delete Entier Row After Finding Name in Array

soulz9,

The error you mentioned, "Select method of Range class failed", can sometimes occur when you try to select a locked cell in a protected worksheet. If your database worksheet is protected, you'll likely have to unprotect it before you can delete rows from it

Good call, but did not resolve my problem. I found the database sheet to have the lock cell feature. Even after disabling it same error occurs.

 

[soulz9]

Here is the delete portion of the code

    Dim SearchVal As Variant       
    Dim RowVal As Variant           
    Dim myRange As Range
    Dim ws As Worksheet
    Set ws = Worksheets("SupplierDB")
    
    
    If Me.txtSearchSup.Value = "" Then
        Me.txtSearchSup.SetFocus
        MsgBox "No Data to Delete"
        Exit Sub
    End If
    
    SearchVal = Me.txtSearchSup.Value        
    Debug.Print SearchVal
    
    RowVal = Application.Match((SearchVal), ws.Range("A:A"), 0)   
    Debug.Print RowVal
    
    ws.Cells(RowVal, 1).EntireRow.Select
    Selection.Delete Shift:=xlUp
    MsgBox "Supplier deleted from database"
    
    Me.txtSearchSup.Value = ""

I hope someone can figure out this irritating problem. Thanks

 

[soulz9]

Anyone?

Here is the delete portion of the code

    Dim SearchVal As Variant       
    Dim RowVal As Variant           
    Dim myRange As Range
    Dim ws As Worksheet
    Set ws = Worksheets("SupplierDB")
    
    
    If Me.txtSearchSup.Value = "" Then
        Me.txtSearchSup.SetFocus
        MsgBox "No Data to Delete"
        Exit Sub
    End If
    
    SearchVal = Me.txtSearchSup.Value        
    Debug.Print SearchVal
    
    RowVal = Application.Match((SearchVal), ws.Range("A:A"), 0)   
    Debug.Print RowVal
    
    ws.Cells(RowVal, 1).EntireRow.Select
    Selection.Delete Shift:=xlUp
    MsgBox "Supplier deleted from database"
    
    Me.txtSearchSup.Value = ""

I hope someone can figure out this irritating problem. Thanks