Thread: Macro to delete the rows automatically

Hi I have a requirement
when a particular row is deleted in sheet1, the corresponding row should be automatically deleted from sheet2. column A in both the sheets is ID field which is unique.Please help to solve this problem

in both sheets those unique ID is in the same row or before delete you have to check ID's position in sheet 2 ?

Best Regards

the unique id in both the sheets is in different rows but they are in column A

hi Maczaq...appreciate for your help..but it is throwing some error in Usedrange when the sheet is opened..can you please see to it

correct file attached

error is solved
multiply row delete now is available too (thanks Domski for info)

Best Regards

thanks a lot...can you please tell me how to import this code so that it works perfectly

hi

Press ALT+F11 in your document (VBA editor should appear)
drop menu View -> Project Explorer (make sure it is open, should be on the left side)

click twice on Sheet1 and paste there a code:

'This will hold the number of rows used in the spreadsheet before the change.
'We’ll compare it to the number of rows after the change to determine if one has been deleted.
Public rowCount As Long
Public rr, rc As Long

Private tmpID() As String  ' memory for remember ID's of selected row

Private Sub Worksheet_Activate()
    rowCount = Me.UsedRange.Rows.Count
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
        If Me.UsedRange.Rows.Count < rowCount Then
         Dim r As Long 'for index of row for ID value in sheet 2
         'dalete row with the same ID in sheet 2
         On Error Resume Next
          'find row(s) in sheet 2 to delete
          For i = 0 To UBound(tmpID)
            r = Sheet2.Range("A:A").Find(tmpID(i), SearchOrder:=xlByRows, Lookat:=xlWhole, SearchDirection:=xlNext).Row
             Sheet2.Rows(r & ":" & r).Delete Shift:=xlUp 'delete in sheet 2
          Next i
        End If
  rowCount = Sheet1.UsedRange.Rows.Count 'set rowCount
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ReDim tmpID(Selection.Rows.Count)
    For i = 0 To Selection.Rows.Count - 1
      tmpID(i) = Cells(Target.Row + i, 1).Value
    Next i
End Sub

Then press twice on ThisWorkbook and past following code

Private Sub Workbook_Open()
 rowCount = Sheet1.UsedRange.Rows.Count 'new rowCount
End Sub

Consider to mark this thread as solved and if you are satisfacted as well please click on my reputation's add button

Best Regards

You have right Domski - provided solution works fine only for one deleted row or for selected rows (row by row)

I will think about update to catch that

Thanks for your ideas
Best Regards

Hello All,

I update the script, now it works for non-contiguous selections.

I hope no more bugs

Best Regards
MaczaQ

hi Maczaq..Thanks a lot for your help..appreciate your efforts to solve the issue...

hi mohan308

I see You have satisfaction and so have I.

Please consider to mark this thread as SOLVED and if you want click on add button to add me reputations points

Best Regards