Find and delete database entries

[banjoflanjo]

Hi,

I have created a database which consists of 7 different tabs which need to be populated with entries. As such I have created a form for inserting new entries (each entry needs to be added to all 7 tabs). Each database range is named sheet1 - sheet7.

What I need is another form for deleting entries eg. enter the database reference number in cell A1, start a macro that finds this entry in all 7 named ranges and deletes the entire row that it finds it on.

Can anyone help with the code that I need for this?

Thanks

Banjoflanjo

 

[pavin]

Say for suppose you have your entry in the first sheet cell A1: And I assume that the value you are finding appears only once in the other sheets.

Try the below code:

Sub test()
My_val = Sheets(1).Range("A1").Value
On Error Resume Next
For i = 2 To Sheets.Count
Sheets(i).Select
Cells.Find(What:=My_val, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

If ActiveCell.Value = My_val Then
ActiveCell.EntireRow.Delete
End If

Next i
End Sub

 

[banjoflanjo]

Thanks for your reply Pavin but this does not do what I need. I'll explain it again and try to be clearer.

I have 7 tabs each containing a different database, each database has a named range - "sheet1", "sheet2", "sheet3", "sheet4", "sheet5", "sheet6" & "sheet7".

Now I have a form and macro which enters new entries onto each of the 7 databases. What I dont have is a form and macro to delete entries.

So if entry 12345 exists in all 7 named ranges. I want to create a new tab for deleting entries, in which I can enter 12345 into say cell A1 and then run a macro which will find the entry entered in cell A1 in each of the 7 database named ranges, and delete the entire row that the entry is on.

Thanks

Banjoflanjo

 

[doofusboy]

If you create a named range 'SearchValue' for the cell in which you enter the value you are looking for, the following should work for what you want to do:

Dim iCTR As Integer
Dim r As Range
Dim searchValue As String
    searchValue = CStr(ThisWorkbook.Names("SearchValue").RefersToRange.Value)
    For iCTR = 1 To 7
        For Each r In ThisWorkbook.Names("Sheet" & iCTR).RefersToRange
            If CStr(r.Value) = searchValue Then
                r.EntireRow.Delete
            End If
        Next r
    Next iCTR

 

[banjoflanjo]

Thanks for that Doofusboy, the code looks good.

For validation purposes would it be possible to get the message "SearchValue has now been removed from the database" upon deletion? And also the message "SearchValue not found in the database" if the search value is not found?

The more info provided in the message the better with regards to which databases the search value was deleted from/not found in.