CODEWITHSUNDEEP
vbaexcel
Jelle Huevo
Jelle Huevo

Reputation: 3

VBA to work on all sheets

I tried many different sample codes before posting this, but I can't get any of them to work.

I need this to run not just in the active sheet but on all sheets in my file.

On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Hope someone is able to help.

Upvotes: 0

Views: 75

Answers (2)

D. O.
D. O.

Reputation: 626

The solution of Alex K. is OK, only check if the sheet is empty or not, otherwise you will have an error with "SpecialCells" :

Dim sheet As Worksheet
For Each sheet In ActiveWorkbook.Worksheets
    If WorksheetFunction.CountA(sheet.Cells) <> 0 Then
        sheet.Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End If
Next
End Sub

Upvotes: 1

Alex K.
Alex K.

Reputation: 175956

You need to loop over the sheets in the workbook and manipulate each one individually:

Dim sheet As Worksheet
For Each sheet In ActiveWorkbook.Worksheets
    sheet.Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Next

Upvotes: 3

Related Questions