Need to delete rows in multiple sheets simultaneously

Posts
1
Registration date
Wednesday May 29, 2019
Status
Member
Last seen
May 29, 2019
- - Latest reply: TrowaD
Posts
2566
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 19, 2019
- Jun 3, 2019 at 11:52 AM
Hi
I have a worksheet that has 3 sheets. Sheet 2 and 3 have data linked from sheet 1.
If I delete a row in sheet 1, I want to have the corresponding row deleted in sheet 2 and 3.
I tried grouping the sheets but that did not help. Please provide suggestions.
I never wrote a VBA code but some people were telling me that its helpful.
Please help.
See more 

1 reply

Posts
2566
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 19, 2019
370
0
Thank you
Hi Ightbet,

Not sure what you mean by "grouping the sheets", but by selecting all sheets (right-click a sheets tab, Select All Sheets) you can delete rows from all sheets.

But since it is fun to use VBA, here is a code which let you select a range, which is then converted to rows (so the range can be B5:B10, to delete rows 5:10) and then deleted from Sheet1, Sheet2 and Sheet3 (assuming those are actually the sheet names you used):
Sub RunMe()
Dim mRange As Range, mSheet As Worksheet

Set mRange = Application.InputBox("Select range", Type:=8)
Set mSheet = ActiveSheet

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Rows(mRange.Row & ":" & mRange.Row + mRange.Rows.Count - 1).Select
Selection.Delete
mSheet.Select
End Sub


Even easier would be to select a range and then hit a shortcut to delete those rows from the 3 sheets.
To assign a shortcut, press Alt+F8 to display available macro's, select the macro and then click "Options".
Here is the code for that:
Sub RunMe2()
Dim tRow, bRow As Integer, mSheet As Worksheet

Set mSheet = ActiveSheet

tRow = Selection.Row
bRow = Selection.Row + Selection.Rows.Count - 1

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Rows(tRow & ":" & bRow).Select
Selection.Delete
mSheet.Select
End Sub


To implement the code(s), press ALT+F11, top menu "Insert", "Module", paste code(s) in the big white field.

Best regards,
Trowa
Respond to TrowaD