Need to delete rows in multiple sheets simultaneously [Closed]

Report
Posts
1
Registration date
Wednesday May 29, 2019
Status
Member
Last seen
May 29, 2019
-
Posts
2621
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 28, 2020
-
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.

1 reply

Posts
2621
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 28, 2020
420
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