Previous Topic Next Topic

Need to delete rows in multiple sheets simultaneously

Closed
ightbet Posts 1 Registration date Wednesday May 29, 2019 Status Member Last seen May 29, 2019 - May 29, 2019 at 11:53 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - 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.

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jun 3, 2019 at 11:52 AM
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
0