Delete row to invoke MACRO ?!

[Dhimit]

Hi,

Can i invoke a MACRO or (anything else) by deleting a ROW in a worksheet to perform another action ?!

I have a MACRO that creates a duplicate record to a TARGET worksheet, so I need another MACRO to do the opposite in the event of deleting a row in SOURCE worksheet.

Dhimit

 

[Damon Ostrander]

Hi Dhimit,

There is no direct way to know if a row has been deleted, but here is an indirect method:

_________________________________________________________

Dim UsedRows As Long

Private Sub Worksheet_Change(ByVal Target As Range)
'Check if entire row modified
If Target.Address = Target.EntireRow.Address Then
If UsedRows = 0 Then Exit Sub
'Check if number of rows has decreased indicating deletion
UsedRows = Me.UsedRange.Rows.Count
If Me.UsedRange.Rows.Count < UsedRows Then
MsgBox "Row Deleted", vbInformation
End If
UsedRows = Me.UsedRange.Rows.Count
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'update UsedRows count in case row added or deleted
UsedRows = Me.UsedRange.Rows.Count
End Sub

_________________________________________________________

This is worksheet event code so must be placed in the Worksheet's code module. To do this, right-click on the source worksheet's tab, select View Code, and paste this code into the Code pane.

This code keeps track of the number of used rows on the sheet in order to determine if a row has been added or deleted, and also to determine which (insertion or deletion) is the case.

 

[Dhimit]

RE: ROW DELETION

Thanks Damon,

How does a 'Worksheet Event Code' work as opposed to a 'Macro' ?
A 'MACRO' is invoked by RUNNING it using the menu or a shortcut key.

My requirement is quite simple :
I have a MACRO that copies data from Worksheet A to Worksheet B.

If I delete a record from WorksheetA, i need a SOLUTION that will delete the corresponding record in WorksheetB !

How exactly will 'Worksheet Event Code' help me achieve this ?

Dhimit

 

[Damon Ostrander]

Hi Dhimit,

Worksheet event code is code that runs when a certain event occurs with a worksheet object. The two events used in my code are the SelectionChange--which 'fires' when the user changes the cell or range of cells selected on the worksheet, and then Change event--which fires when the user changes a cell or range of cells, such as adding or removing rows or columns, deleting cell contents, etc.

To call your macro when a row is deleted simply replace the MsgBox statement in the Change event with the call to your macro, like this.

Private Sub Worksheet_Change(ByVal Target As Range) 
'Check if entire row modified 
If Target.Address = Target.EntireRow.Address Then 
If UsedRows = 0 Then Exit Sub 
'Check if number of rows has decreased indicating deletion 
UsedRows = Me.UsedRange.Rows.Count 
If Me.UsedRange.Rows.Count < UsedRows Then 
Macro1    'Creates a duplicate record in the target worksheet
End If 
UsedRows = Me.UsedRange.Rows.Count 
End If 
End Sub

I suggest that you install the code exactly as I provided previously and then try making changes to the worksheet, including adding and deleting rows. This should satisfy you that the code is working properly. Then add the call to Macro1. Of course, I am presuming that you have Macro1 already written. If Macro1 is supposed to insert the deleted row into another worksheet, the code I provided will need to be modified a bit, because by the time Macro1 is executed the row will already be gone.

I hope this answers your question.

Keep Excelling.

Damon

 

[Dhimit]

RE:

Thanks Damon,

Do I need to incorporate some kind of a 'LOOP' in the code to delete the matching corresponding record in 'DESTINATION' worksheet when i delete a record from 'SOURCE' worksheet ?

Dhimit :wink:

 

[Damon Ostrander]

Hi again Dhimit,

You shouldn't need a loop, but a search might be required to find the corresponding record if your two sheets are not organized such that the rows are the same.

If the rows are the same (e.g., if you delete row 5 from the source worksheet then row 5 on the destination worksheet should be also deleted), then it is quite simple and doesn't need to be done by a macro, but just the code

Worksheets("WorksheetB").Rows(Target.Row).Delete

If the rows are not the same, then how to do the search to find the matching row depends on how many columns have to be matched to identify the corresponding row.

Damon

Damon

 

[Dhimit]

RE:

Damon,

A loop is DEFINATELY required because there's more than one 'SOURCE' worksheets from which the DATA end up in a single 'TARGET' worksheet !

Dhimit