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
Bookmarks