CODEWITHSUNDEEP
excelvba
Userbitcoin4life1
Userbitcoin4life1

Reputation: 15

How to delete Empty Range for entire workbook excluding a few sheets?

In my workbook, I want to go through each sheet, excluding 3 specific sheets, and check the range A8:A12. Within that range, if the cell is empty, I would like to delete the entire row. This is what I have so far:

Sub deleteRows()

   Dim QSh As Worksheet
   
   Dim rngDel As Range
   
   Set rngDel = Range("A8:A12")

   For Each QSh In ThisWorkbook.Sheets

        If QSh.Name <> "Sheet1" And QSh.Name <> "Sheet2" And QSh.Name <> "Sheet3" Then
    
        QSh.rngDel.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        
    End If

End Sub

Doesn't seem to work, can anyone help?

Upvotes: 0

Views: 40

Answers (1)

Robert Todar
Robert Todar

Reputation: 2145

Here are three things I see

  • rngDel is set before looping each worksheet, meaning it is implicitly bound only to the ActiveSheet. What you have is the same thing as Set rngDel = Activesheet.Range("A8:A12")
  • QSh.rngDel is incorrect, as rngDel is a variable and not a property of QSh.
  • There is no Next for your For Loop.

One additional thing from @bigben is that there needs to be some type of error handling for when/if there are no blank cells.

With that, this should work using your code:

Sub deleteRows()
   Dim QSh As Worksheet
    For Each QSh In ThisWorkbook.Sheets
        If QSh.Name <> "Sheet1" And QSh.Name <> "Sheet2" And QSh.Name <> "Sheet3" Then
            On Error Resume Next
                QSh.Range("A8:A12").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
            On Error GoTo 0
        End If
    Next ' <~ Missing
End Sub

Upvotes: 1

Related Questions