Delete Data from Database Automatically

How to delete data from database in an Excel worksheet automatically with VBA. Watch the video below:

Watch the video on YouTube.

Here’s the complete VBA code:

  1. How to delete data from the database.

Sub deleteRecord()
Dim reply As String
reply = InputBox(“Are you sure you wish to delete the record? This action cannot be undone! Delete anyway? Then type y”)
If reply = “y” Then
currentrow = Cells(4, 1)
Cells(currentrow, 1).EntireRow.Delete
Else
Range(“E2″).Select
Exit Sub
End If
End Sub

2. How to add data and avoid duplicate entries in a database

Sub addData()
Dim i As Long, lastrow As Long, nextBlankRow As Long
lastrow = Cells.Find(What:=”*”, _
After:=Range(“A1”), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

nextBlankRow = lastrow + 1

If Range(“E2”) = “” Then
MsgBox “You didn’t enter the first name of the customer!”
Range(“E2”).Select
Exit Sub
End If

If Range(“E3”) = “” Then
MsgBox “You didn’t enter the last name of the customer!”
Range(“E3”).Select
Exit Sub
End If

Cells(nextBlankRow, 3) = Range(“E2”)
Cells(nextBlankRow, 4) = Range(“E3”)
Cells(nextBlankRow, 5) = Range(“E4”)
Cells(nextBlankRow, 6) = Range(“E5”)
Cells(nextBlankRow, 7) = Range(“E6”)
Cells(nextBlankRow, 8) = Range(“E7”)
Cells(nextBlankRow, 9) = Range(“E8”)
‘MsgBox nextBlankRow
Dim p As Long, q As Long
p = 13
q = p + 1
Do While Cells(p, 3) <> “”
Do While Cells(q, 3) <> “”
If Cells(p, 3) = Cells(q, 3) And Cells(p, 4) = Cells(q, 4) Then
MsgBox “Duplicate Data! Will be removed from database!”
Range(Cells(q, 3), Cells(q, 9)).ClearContents
Else
q = q + 1
End If
Loop
p = p + 1
q = p + 1
Loop

End Sub

3. Clear or Reset data

Sub resetData()
Range(“E2:E8″).ClearContents
End Sub

4. Check for existing data or search for data in a databse

Sub checkExistingData()

Dim i As Long, lastrow As Long

lastrow = Cells.Find(What:=”*”, _
After:=Range(“A1”), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

If Range(“E2”) = “” Then
MsgBox “You didn’t enter the first name of the customer!”
Range(“E2”).Select
Exit Sub
End If

If Range(“E3”) = “” Then
MsgBox “You didn’t enter the last name of the customer!”
Range(“E3”).Select
Exit Sub
End If

For i = 13 To lastrow

If Cells(i, 3) = Range(“E2”) And Cells(i, 4) = Range(“E3”) Then
Range(“E4”) = Cells(i, 5)
Range(“E5”) = Cells(i, 6)
Range(“E6”) = Cells(i, 7)
Range(“E7”) = Cells(i, 8)
Range(“E8”) = Cells(i, 9)
Cells(4, 1) = i
Exit Sub
End If
Next i

MsgBox “Record doesn’t exist”

End Sub

5. How to display picture or image next to the database

Sub displayPic()
Application.ScreenUpdating = False
Dim myObj
Dim Pictur
Set myObj = ActiveSheet.DrawingObjects
For Each Pictur In myObj
If Left(Pictur.Name, 7) = “Picture” Then
Pictur.Select
Pictur.Delete
End If
Next

Dim EmployeeName As String, T As String

myDir = “C:\Users\takyar\Pictures\employees\”
EmployeeName = Range(“E2”) & Range(“E3”)
T = “.jpg”

‘Range(“F2”).Value = EmployeeName
On Error GoTo errormessage:
ActiveSheet.Shapes.AddPicture Filename:=myDir & EmployeeName & T, linktofile:=msoFalse, savewithdocument:=msoTrue, Left:=420, Top:=15, Width:=60, Height:=60

errormessage:
If Err.Number = 1004 Then
MsgBox “File does not exist.” & vbCrLf & “Check the name of the employee!”
Range(“E2”).Value = “”
Range(“E3”).Value = “”
End If

Application.ScreenUpdating = True

End Sub

6. How to update records in a database

Sub updateRecord()
Dim reply As String
Dim currentrow As Long

reply = InputBox(“Are you sure you wish to update the record? Type y to update”)
If reply = “y” Then
currentrow = Cells(4, 1)
Cells(currentrow, 3) = Range(“E2”)
Cells(currentrow, 4) = Range(“E3”)
Cells(currentrow, 5) = Range(“E4”)
Cells(currentrow, 6) = Range(“E5”)
Cells(currentrow, 7) = Range(“E6”)
Cells(currentrow, 8) = Range(“E7”)
Cells(currentrow, 9) = Range(“E8”)
Else
Exit Sub
Range(“E2”).Select

End If

End Sub

Create Database in Excel Worksheet Quickly
Create Database in Excel Worksheet Quickly

Download the sample File