CODEWITHSUNDEEP
vbaexcel
Hags
Hags

Reputation: 27

How can I delete the same row in multiple sheets without repeating code?

Sheets("CDGL Data").Select
Rows([1]).EntireRow.Delete

Sheets("CDGL Data (2)").Select
Rows([1]).EntireRow.Delete

Sheets("CDGL Data (3)").Select
Rows([1]).EntireRow.Delete

Sheets("CDGL Data (4)").Select
Rows([1]).EntireRow.Delete

Upvotes: 0

Views: 494

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Regroup them into an Array:

Sheets(Array("CDGL Data", "CDGL Data (2)", "CDGL Data (3)", "CDGL Data (4)")).Select
Rows("1:1").Delete Shift:=xlUp

(based on macro-recorder code, great for discovering the Excel object model and API)

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152505

You will want to iterate through the worksheets and delete the row:

Sub delrow()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If Left(ws.Name, 9) = "CDGL Data" Then
        Rows(1).Delete
    End If
Next ws
End Sub

This will delete the row from every sheet that has CDGL Data as its first 9 characters.

If you want to be more specific then we can create a string with all the name of the sheets concatenated. Then see if the sheet name is in the string:

Sub delrow()
Dim ws As Worksheet
Dim wsname As String
wsname = "CDGL Data,CDGL Data (2),CDGL Data (3),CDGL Data (4)"
For Each ws In ThisWorkbook.Worksheets
    If InStr(wsname, ws.Name) Then
        Rows(1).Delete
    End If
Next ws
End Sub

Upvotes: 1

Related Questions