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