How to delete empty rows in all Sheets

Question

I am trying to delete empty rows in every sheet using this code in Excel 2010:

Private Sub CommandButton1_Click()

Dim I As Integer

'For all sheets...

For I = 1 To Sheets.Count

    'select corresponding sheet

    Sheets(I).Select

    Sheets(I).Activate

    'write delete code

    For fila = 1 To 10

        If Cells(fila, 4).Value = "" Then

            Rows(fila).Delete

        End If

    Next fila

    'Go to next sheet

Next

End Sub

This code only deletes rows on my first active sheet.

Answer 1

Maybe this solution will help you : It will clean all worksheets in your workbook and delete empty rows. In the end, msg box will tell you the percentage of rows that were deleted for each sheet. Best regards,

Sub Clean()
Dim Sht As Worksheet, DCell As Range, Calc As Long, Rien As String, Avant As Double, plage As Range
On Error Resume Next
Calc = Application.Calculation ' ---- mémorisation de l'état de
recalcul
'------------------------------------------------------------
MsgBox "Pour le classeur actif : " _
& Chr(10) & ActiveWorkbook.FullName _
& Chr(10) & "dans chaque feuille de calcul" _
& Chr(10) & "recherche la zone contenant des données," _
& Chr(10) & "réinitialise la dernière cellule utilisée" _
& Chr(10) & "et optimise la taille du fichier Excel", _
vbInformation, _
"d'après LL par <a href="mailto:GeeDee@m6net.fr">GeeDee@m6net.fr</a>"
'-------------------------------------------------------------
MsgBox "Taille initiale de ce classeur en octets" _
& Chr(10) & FileLen(ActiveWorkbook.FullName), _
vbInformation, ActiveWorkbook.FullName
'------------------------------------------------------------
With Application
.Calculation = xlCalculationManual
.StatusBar = "Nettoyage en cours..."
.EnableCancelKey = xlErrorHandler
.ScreenUpdating = True
End With
'-------------------- le traitement
For Each Sht In Worksheets
Avant = Sht.UsedRange.Cells.Count
Application.StatusBar = Sht.Name & "-" & Sht.UsedRange.Address
'-------------------Traitement de la zone trouvée
If Sht.UsedRange.Address <> "$A$1" Or Not IsEmpty(Sht.[A1]) Then
Set DCell = Sht.Cells.Find("*", , , , xlByRows, xlPrevious)(2)
'----------------Suppression des lignes inutilisées
If Not DCell Is Nothing Then
Sht.Range(DCell, Sht.Cells([A:A].Count, 1)).EntireRow.Delete
Set DCell = Nothing
Set DCell = Sht.Cells.Find("*", , , , xlByColumns, xlPrevious)(, 2)
'----------------Suppression des colonnes inutilisées
If Not DCell Is Nothing Then Sht.Range(DCell, Sht.[IV1]).EntireColumn.Delete
End If
Rien = Sht.UsedRange.Address
End If
ActiveWorkbook.Save
'---------------------Message pour la feuille traitée
MsgBox "Nom de la feuille de calcul :" _
& Chr(10) & Sht.Name _
& Chr(10) & Format(Sht.UsedRange.Cells.Count / Avant, "0.00%") & " de la taille initiale", _
vbInformation, ActiveWorkbook.FullName
Next Sht
'--------------------Message fin de traitement
MsgBox "Taille optimisée de ce classeur en octets " & Chr(10) & FileLen(ActiveWorkbook.FullName), _
vbInformation, _
ActiveWorkbook.FullNameActive
'--------------------
Application.StatusBar = False
Application.Calculation = Calc
End Sub

Answer 2

Always remember to loop backward when deleting objects (in your case rows), so use For i = 10 to 1 Step -1.

Also, try to avoid using Select and Activate, instead you could directly reference the Worksheet or Range. In this case use directly the ws defined as Worksheet, to see if If ws.Cells(fila, 4).Value = ""

Code

Option Explicit

Private Sub CommandButton1_Click()

Dim I As Integer, fila As Long
Dim ws As Worksheet

' loop through all worksheets
For Each ws In ThisWorkbook.Worksheets
    ' loop backwards when deleting objects
    For fila = 10 To 1 Step -1
        If ws.Cells(fila, 4).Value = "" Then ws.Rows(fila).Delete
    Next fila
Next ws

End Sub