Maxicus
03-02-2018, 06:47 AM
Hi hope someone could help me with this one.
I am working on a code that will delete 5 specific cells based on the value found on a textbox.
so i have worksheet with 5 columns:
ID
NAME
DATE
ITEM
AMOUNT
11
Max
0203180327
Ice
1
15
John
0202180400
Lamp
2
11
Max
0203180328
Lamp
1
13
Michael
0202180815
Bucket
1
I also have a textbox and a delete button on a userform, if the value in the textbox equals the date found in the table above, the delete button will be active.
for example: if the textbox contains "0203180328" it has to delete that entry along with the 2 cells before and after it, moving the bottom entry one up.
I don't want it to delete the row, i only want it to delete the first 5 cells (11 - Max - 0203180328 - Lamp - 1) and move the bottom entry up one. deleting the entire row also deletes some other data i have on the sheet.
my current code is as follow:
Private Sub CommandButton2_Click()
'declare the variables
Dim findvalue As Range
Dim cDelete As VbMsgBoxResult
'check for values
If Emp1.Value = "" Then
MsgBox "There is not data to delete"
Exit Sub
End If
'give the user a chance to change their mind
cDelete = MsgBox("Are you sure that you want to delete this Issued Item from all records?", vbYesNo + vbDefaultButton2, "Are you sure????")
If cDelete = vbYes Then
'delete thecells
Set findvalue = Sheet7.Range("C:C").Find(what:=Emp1, LookIn:=xlValues)
findvalue.Cells.DELETE
End If
End Sub
My code now only deletes the date and also shifts the cells left.
I need it to delete the 5 cells as mentioned and move the cells up not left.
if i replace "findvalue.Cells.DELETE" with "findvalue.EntireRow.DELETE" it deletes the entire row but also deletes other data i have on the same sheet. i don't want to move this data to a different sheet if there is an alternative.
I am working on a code that will delete 5 specific cells based on the value found on a textbox.
so i have worksheet with 5 columns:
ID
NAME
DATE
ITEM
AMOUNT
11
Max
0203180327
Ice
1
15
John
0202180400
Lamp
2
11
Max
0203180328
Lamp
1
13
Michael
0202180815
Bucket
1
I also have a textbox and a delete button on a userform, if the value in the textbox equals the date found in the table above, the delete button will be active.
for example: if the textbox contains "0203180328" it has to delete that entry along with the 2 cells before and after it, moving the bottom entry one up.
I don't want it to delete the row, i only want it to delete the first 5 cells (11 - Max - 0203180328 - Lamp - 1) and move the bottom entry up one. deleting the entire row also deletes some other data i have on the sheet.
my current code is as follow:
Private Sub CommandButton2_Click()
'declare the variables
Dim findvalue As Range
Dim cDelete As VbMsgBoxResult
'check for values
If Emp1.Value = "" Then
MsgBox "There is not data to delete"
Exit Sub
End If
'give the user a chance to change their mind
cDelete = MsgBox("Are you sure that you want to delete this Issued Item from all records?", vbYesNo + vbDefaultButton2, "Are you sure????")
If cDelete = vbYes Then
'delete thecells
Set findvalue = Sheet7.Range("C:C").Find(what:=Emp1, LookIn:=xlValues)
findvalue.Cells.DELETE
End If
End Sub
My code now only deletes the date and also shifts the cells left.
I need it to delete the 5 cells as mentioned and move the cells up not left.
if i replace "findvalue.Cells.DELETE" with "findvalue.EntireRow.DELETE" it deletes the entire row but also deletes other data i have on the same sheet. i don't want to move this data to a different sheet if there is an alternative.