Delete row to invoke MACRO ?!

Dhimit

Board Regular
Joined
Jul 24, 2007
Messages
195
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

Board Regular
Joined
Jul 24, 2007
Messages
195
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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.

Code:
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

Board Regular
Joined
Jul 24, 2007
Messages
195
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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

Board Regular
Joined
Jul 24, 2007
Messages
195
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
 

Forum statistics

Threads
1,181,102
Messages
5,928,063
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top