excel vba to delete blank rows in a range across multiple worksheets

Question

Hello I am trying to delete rows in the range from 7 to 31 if the data in column A is blank. However i want this to run all worksheets other than the ones i specify. I have attempted the code but it doesn't work, can anyone give me a pointer on where i went wrong? Also should i be putting an error handling for if there isn't any blank cells?

Sub deletespaces()
Dim ws As Worksheet
Dim rng As Range

For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Menu" And ws.Name <> "Paste here" And ws.Name <> "Data" Then

 Range("a7:A31").Select
    Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 End If
Next ws
End Sub

BigBen · Accepted Answer · 2020-12-09T18:35:26.067

1

Avoid Select.
Qualify the Range with the worksheet ws.
Test first for blank cells.

If ws.Name <> "Menu" And ws.Name <> "Paste here" And ws.Name <> "Data" Then
    Dim visibleCells As Range  
    Set visibleCells = Nothing

    On Error Resume Next
    Set visibleCells = ws.Range("a7:A31").SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0

    If Not visibleCells Is Nothing Then
        visibleCells.EntireRow.Delete
    End If
End If

edited Dec 09 '20 at 18:35

answered Dec 09 '20 at 18:25

BigBen

46,229
7
24
40

thanks big ben so how would i get this to loop through my worksheets? I tried adding it after the then on the original code but got "next without for" – Classmaz Dec 09 '20 at 18:32
Probably missing an `End If`... see the edit for what the code inside the loop should be. – BigBen Dec 09 '20 at 18:35
i am now getting a runtime error 91 object variable or with block variable not set – Classmaz Dec 09 '20 at 18:43
On what line please? – BigBen Dec 09 '20 at 18:44
Sorry BigBen i mistyped a worksheet name and that was what created the error. Really appreciate your help – Classmaz Dec 09 '20 at 19:13

excel vba to delete blank rows in a range across multiple worksheets

1 Answers1