PDA

View Full Version : Deleting Data from Database using VBA



OrangAsli
01-09-2018, 03:28 AM
Hi everyone :)

I'm facing a challenge that I can't seem to solve. I have to create a database using input solely via a GUI. Basically the user inputs different info via textboxes and hits an "add" button and the information is added to the spreadshett and displayed as follows:


1. *** *** ***
2. *** *** ***
3. *** *** ***

Now I need to add the feature that another GUI will let you delete data entries. What I have so far is a textbox in which the user can enter the position of the entry he wants to delete (1,2,3 etc.) and then hit a button. The code I'm using is the following:

Private Sub btndeletebond_Click()
'Take number input from GUI textbox and find in spreadsheet

Set lookuprow = Sheet1.Range("A:A").Find(What:=deletenrtxt.Value, LookIn:=xlValues)
x = lookuprow.Row

'Delete rows corresponding to textbox input
Rows(x).EntireRow.Delete
End Sub

I now have two problems.

First problem: The code deletes the entire row from the spreadsheet (yes I took an easy route and this backfires now) which means that other data in the same row at a different point in the spreadsheet is deleted.

Second Problem: The entry number does not adapt. If I delete the 2. entry in the example above the 3. entry moves up to the position of the 2. but remains "labeled" as 3.

Can anyone help me solve the issue? I need a code that only clears the content of the cells of the selected entry (without deleting the entire row in the spreadsheet) and then renumbers the other entries that moved up a row.

Hope my issue is clear :D I appreciate all the help!

offthelip
01-09-2018, 05:06 AM
this code should do it, it uses deltenrtxt as a calling parameter


Sub dele(deletenrtxt As Long)
Dim outarr() As Variant


lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 4))
ReDim outarr(1 To lastrow, 1 To 4) As Variant
indi = 0
For i = 1 To lastrow
If inarr(i, 1) <> deletenrtxt Then
For k = 2 To 4
outarr(i - indi, k) = inarr(i, k)
Next k
outarr(i - indi, 1) = inarr(i, 1) - indi
Else
indi = 1
End If
Next i
If indi = 1 Then
For k = 1 To 4
outarr(lastrow, k) = ""
Next k
End If


Range(Cells(1, 1), Cells(lastrow, 4)) = outarr


End Sub