Public Sub dele1()
Dim rng As Range
Dim rng1 As Range
Dim rng2 as Range
Dim ans as Long
Dim res as Variant
Dim srh as Variant
srh = Worksheets("DataManagement").Range("b2")
With Sheets("LINELIST")
Set rng = .Range(.Range("a1"), _
.Range("a1").End(xlDown))
End With
res = Application.Match(src,rng,0)
if iserror(res) then
msgbox srh & " not found"
exit sub
End if
set rng1 = rng(res)
ans = Msgbox("Delete Record?", _
"Delete this record?", vbYesNo)
if ans = vbNo then
exit sub
End If
With Sheets("DeletedRecords")
set rng2 = .Range("A1").End(xlDown) _
.Offset(1, 0)
End With
rng1.EntireRow.copy
rng2.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
rng1.EntireRow.Delete
Msgbox ("Idcode" & srh & "deleted from dataset")
End Sub
--
Regards,
Tom Ogilvy
"Shinu" wrote in message
...
Hi,
I am new to vba codes. I have some how manage to write the below given
code, but it is giving me error "Application defined or object defined
error"
My objective is :
To delete a record and before deleting, copy that
record to another sheet to keep track of deleted records
Sheets("linelist") is the dataset. Column A contains the idcode
In Sheets("DataMangement"), cell B2 contains the value(idcode) to be
searched in sheet linelist.
Searh for that record in sheet linelist, once found, copy the entire
row to another sheet ("DeletedRecords") at the last row which is empty
and then delete that record from sheet("linelist")
I hope so I have made my objetive clear.
In addition I also would like a confirmation from user whether to
delete the record or not. I have no idea of how to do that.
My code as follows:-
Private Sub dele1()
Dim rng As Range
Dim drng As Range
Dim n As Long
' Dim nr As Long
'nr = rng.Rows.Count
srh = Worksheets("DataManagement").Range("b2")
Sheets("LINELIST").select
With Sheets("LINELIST")
Set rng = .Range(.[a1], .[a1].End(xlDown))
End With
Set drng = rng.Cells
drng = rng.Value
For Each c In drng
If srh = c Then
drng.EntireRow.Copy
Sheets("DeletedRecords").Select
Range("A1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("linelist").Select
drng.EntireRow.delete
End If
Next
Msgbox ("Idcode" & srh & "deleted from dataset")
End Sub
--
ShinuPosted from - http://www.officehelp.in