Delete Multiple Rows in Multiple Worksheets - R1

[Rumpkin]

I don't have a solution to my original post and am re-posting.
I need to be able to delete multiple rows that are selected consecutively in multiple worksheets.
At this point I would even use some VBA so achieve it.

Goal: select multiple (consecutive) rows within multiple (quantity determined by the sheets selected) work sheets

I have located a VBA solution from (Exceltip) that only deletes one row:

Sub DeleteRowsAllSheets()

   Dim SheetsArr As Variant
   Dim x As Long
   Dim a As Long
 
      SheetsArr = Array("Sheet4","Sheet5","Sheet6","Sheet7","Sheet8","Sheet9","Sheet10","Sheet11","Sheet12","Sheet13","Sheet14","Sheet15","Sheet28","Sheet29","Sheet30","Sheet31","Sheet32","Sheet33","Sheet34","Sheet35","Sheet36","Sheet37","Sheet38","Sheet39")

    a = Selection.Row

    For x = LBound(SheetsArr) To UBound(SheetsArr)
      Sheets(SheetsArr(x)).Rows(a).EntireRow.Delete
Next x

End Sub

This gives me a subscript out of range error and to Debug, highlights "Sheets(SheetsArr(x)).Rows(a).EntireRow.Delete"

When I try to use: (which indicates the sheets/tabs names)

Sub DeleteRowsAllSheets()

   Dim SheetsArr As Variant
   Dim x As Long
   Dim a As Long
 
      SheetsArr = Array("PA.01", "PA.02", "PA.03", "PA.04", "PA.05", "PA.06", "PA.07", "PA.08", "PA.09", "PA.10", "PA.11", "PA.12", "PA.13", "PA.14", "PA.15", "PA.16", "PA.17", "PA.18", "PA.19", "PA.20", "PA.21", "PA.22", "PA.23", "PA.24")

    a = Selection.Row

    For x = LBound(SheetsArr) To UBound(SheetsArr)
      Sheets(SheetsArr(x)).Rows(a).EntireRow.Delete
Next x

End SubSub

It will only delete the top row of the rows selected which then leave #ref in to remaining rows that were to be deleted.

 

[Fluff]

Try

Sub DeleteRowsAllSheets()

   Dim SheetsArr As Variant
   Dim x As Long
   Dim a As String
 
      SheetsArr = Array("PA.01", "PA.02", "PA.03", "PA.04", "PA.05", "PA.06", "PA.07", "PA.08", "PA.09", "PA.10", "PA.11", "PA.12", "PA.13", "PA.14", "PA.15", "PA.16", "PA.17", "PA.18", "PA.19", "PA.20", "PA.21", "PA.22", "PA.23", "PA.24")

    a = Selection.Address

    For x = LBound(SheetsArr) To UBound(SheetsArr)
      Sheets(SheetsArr(x)).Range(a).EntireRow.Delete
Next x

End Sub

 

[Rumpkin]

SheetsArr

Can we make this refer to whatever sheet is selected?

Also getting the error code 13 mismatch, referred to: a = Selection.Address

 

[Fluff]

Did you change the variable type?

 

[Rumpkin]

I don't believe so.
I added one sheet

Sub DeleteRowsAllSheets()

Dim SheetsArr As Variant
Dim x As Long
Dim a As Long

SheetsArr = Array("PA.01", "PA.02", "PA.03", "PA.04", "PA.05", "PA.06", "PA.07", "PA.08", "PA.09", "PA.10", "PA.11", "PA.12", "SU.01", "PA.13", "PA.14", "PA.15", "PA.16", "PA.17", "PA.18", "PA.19", "PA.20", "PA.21", "PA.22", "PA.23", "PA.24")

a = Selection.Address

For x = LBound(SheetsArr) To UBound(SheetsArr)
Sheets(SheetsArr(x)).Rows(a).EntireRow.Delete
Next x

End Sub

 

[Fluff]

You need to change a to string as I showed in the code I posted.

 

[Rumpkin]

Oh, crap. There's that follow directions thing.
My apologies. I just changed the selection.Address.
It works now.
If I delete some of the sheets in the workbook, will it break the code?
Thanks for your help and again, my apologies.

 

[Fluff]

It will if those sheets are still in the array. However you could use

Sub DeleteRowsAllSheets()

   Dim SheetsArr As Variant
   Dim x As Long
   Dim a As String
 
      SheetsArr = Array("PA.01", "PA.02", "PA.03", "PA.04", "PA.05", "PA.06", "PA.07", "PA.08", "PA.09", "PA.10", "PA.11", "PA.12", "PA.13", "PA.14", "PA.15", "PA.16", "PA.17", "PA.18", "PA.19", "PA.20", "PA.21", "PA.22", "PA.23", "PA.24")

    a = Selection.Address

   For x = LBound(SheetsArr) To UBound(SheetsArr)
      If Evaluate("isref('" & SheetsArr(x) & "'!A1)") Then
         Sheets(SheetsArr(x)).Range(a).EntireRow.Delete
      End If
   Next x

End Sub

 

[Rumpkin]

Bingo!
Thank you.
I can't find a shortcut key that will work, though.

 

[Fluff]

You're welcome & thanks for the feedback.

Have a look here for how to assign a shortcut 2 Ways to Assign Keyboard Shortcuts to Macros - Excel Campus