VBA clear database entry.

ipon70

Board Regular
Joined
May 8, 2013
Messages
82
Office Version
  1. 2016
Platform
  1. Windows
I have a page called "ENTRY", this is where everyone puts the information in the database page called "db".
Occasionally they need to review an entry and possibly delete that transactions information. So they go to a page called "EDIT" and put in a transaction number in cell "M4".
This returns everything from the "db" page so they can read it.
If in fact they want to clear that entry, I need them to be able to press delete, and have it use that "id" and clear the contents from "B:DB". Not actually delete the row.
The id does not match the row number, and I need it to also enter the words "DELETED" in the first column of information "B".
Man I hope that makes sense.

EXAMPLE: Transaction 99 is entered on the edit page. It returns everything from that transaction, technically on row 156. So they press clear and now transaction id 99 now shows "DELETED" in the "B" column, and everything else from "C:DB" is cleared out.

Thank you in advance.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi ipon70
just use this Function
VBA Code:
Function DeleteFromDB(ID As Long)
    Dim cell As Range
    With ThisWorkbook.Sheets("db")
        Set cell = (.Range("A:A").Find(ID, lookat:=xlWhole, LookIn:=xlValues))
        cell.Offset(, 1).Value = "DELETED"
        Range(cell.Offset(, 2), cell.Offset(, 105)).ClearContents
    End With
End Function

You can use it like this
VBA Code:
DeleteFromDB 13
 
Upvote 0
Hi ipon70
just use this Function
VBA Code:
Function DeleteFromDB(ID As Long)
    Dim cell As Range
    With ThisWorkbook.Sheets("db")
        Set cell = (.Range("A:A").Find(ID, lookat:=xlWhole, LookIn:=xlValues))
        cell.Offset(, 1).Value = "DELETED"
        Range(cell.Offset(, 2), cell.Offset(, 105)).ClearContents
    End With
End Function

You can use it like this
VBA Code:
DeleteFromDB 13
I will give this a try and thank you so much.
 
Upvote 0
I will give this a try and thank you so much.
Ok, I am unclear as to how to reference the "M4" cell. You mention using it like DeleteFromDB 13. What I did was stick the code into a module and then make a call to it after a question was asked to the user. How do I reference the "M4" cell to tell it, this is what you want to search for?

Thanks and sorry for being thick.
 
Upvote 0
Ok, so all of a sudden something isn't working right and its taking FOREVER to finish this code run.

Here is the old code and it ran a little slow but was done in about 10 seconds. DF1 is where the ID number is at so it knows what line to delete.
Sub db_delete_entry()
Application.ScreenUpdating = False
Worksheets("db").Activate
Dim CompId As Range
Set CompId = Range("A:A").Find(what:=Range("DF1").Value, LookIn:=xlValues, lookat:=xlWhole)
CompId.Offset(, 1).Value = "DELETED"
UserName = Environ("username")
CompId.Offset(, 106).Value = UserName
CompId.Offset(, 107).Value = Date
Range(CompId.Offset(, 2), CompId.Offset(, 105)).ClearContents
Application.ScreenUpdating = True
End Sub

Here is the new code that is just running and running and running forever, take like a minute to get done. DS1 is where the ID number is at so it knows what line to delete.
Sub db_delete_entry()
Application.ScreenUpdating = False
Worksheets("db").Activate
Dim CompId As Range
Set CompId = Range("A:A").Find(what:=Range("DS1").Value, LookIn:=xlValues, lookat:=xlWhole)
CompId.Offset(, 1).Value = "DELETED"
UserName = Environ("username")
CompId.Offset(, 119).Value = UserName
CompId.Offset(, 120).Value = Date
Range(CompId.Offset(, 2), CompId.Offset(, 118)).ClearContents
Application.ScreenUpdating = True
End Sub


Any ideas what is going wrong?? Or what I did wrong, because it was working with the old code the new code works but Christmas is coming.
 
Upvote 0
2 things
Does the ID exist in column A?
Secondly. How long does your sheet take to calculate?

In the first we will set up a message box letting the user know.

In the second we can turn off calculation for the workbook at the start of the code and turn it back on afyer
 
Upvote 0
2 things
Does the ID exist in column A?
Secondly. How long does your sheet take to calculate?

In the first we will set up a message box letting the user know.

In the second we can turn off calculation for the workbook at the start of the code and turn it back on afyer
You were dead on right, I turned off a calculation sheet and voila faster then it was even before. All happens in about 1 second now. Thank you so much for steering me in the right direction.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top