Delete data subject to Condition

Need help with a VB on this, I have a sheet which contains the list of employees(exhibit-1) and another sheet which contains compensation details of these employees (exhibit-2), once the user inputs the ID of the employee in the General Information tab (exhibit-3), i want the macro to go to Exhibit-2 and retain information only for that assignee and delete the other assignee information.

Exhibit-1

Exhibit-2

Exhibit-3

 

Expert Asked on January 26, 2018 in VBA: Macros.
Add Comment
1 Answer(s)

Here is what I came up with.

RE: Delete data  subject to Condition

[Code]

Sub DelCompRecords()
Dim ID As Variant
Dim wsC As Worksheet
Dim wsG As Worksheet
Dim rng As Range
Dim lops As Integer
Dim i As Integer

ID = Worksheets(“General_Info”).[B9]
Set wsC = Worksheets(“Compensation”)
Set wsG = Worksheets(“General_Info”)
Set rng = wsC.Range(“A:A”)
lops = Application.CountA(rng) – 1

Application.ScreenUpdating = False
wsC.Activate
Cells(2, 1).Select

For i = 1 To lops
If ActiveCell <> ID Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1).Select
End If
Next i
wsG.Activate

End Sub

Expert Answered on January 26, 2018.

thank you

on January 27, 2018.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.