• Ask a Question
150
Insert Image Size must be less than < 5MB.
    Ask a Question
    Cancel
    150
    More answer You can create 5 answer(s).
      Ask a Poll
      Cancel
      Top Contributor

      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

       

      Asked by pranab79in on January 26, 2018 in VBA: Macros.
      1 Answers
      Expert

      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

      Answered by Hyside2 on January 26, 2018..
      Cancel
      Add comment