Forum Discussion

ShazSh's avatar
ShazSh
Brass Contributor
Oct 07, 2021

Delete Empty Rows Quickly Looping though all workbooks in Folder

I have more than 200 workbooks in an Folder, and i deletes the empty rows by giving an Range in the code that is Set rng = sht.Range("C3:C50000").

 

If Column C any cell is empty then delete entire Row. Day by day data is enhancing and below code took nearly half hour to complete the processing. That time limit is also increasing with the data.

 

I am looking for a way to to do this in couple of minutes or in less time. I hope to get some help.

 

Sub Doit()
    Dim xFd         As FileDialog
    Dim xFdItem     As String
    Dim xFileName   As String
    Dim wbk         As Workbook
    Dim sht         As Worksheet
    
    Application.ScreenUpdating = FALSE
    Application.DisplayAlerts = FALSE
    
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    If xFd.Show Then
        xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
    Else
        Beep
        Exit Sub
    End If
    xFileName = Dir(xFdItem & "*.xlsx")
    Do While xFileName <> ""
        Set wbk = Workbooks.Open(xFdItem & xFileName)
        For Each sht In wbk.Sheets
            
            Dim rng As Range
            Dim i   As Long
            Set rng = sht.Range("C3:C5000")
            With rng
                'Loop through all cells of the range
                'Loop backwards, hence the "Step -1"
                For i = .Rows.Count To 1 Step -1
                    If .Item(i) = "" Then
                        'Since cell Is empty, delete the whole row
                        .Item(i).EntireRow.Delete
                    End If
                Next i
            End With
    
        Next sht
        wbk.Close SaveChanges:=True
        xFileName = Dir
    Loop

    Application.ScreenUpdating = TRUE
    Application.DisplayAlerts = TRUE
End Sub

3 Replies

  • ShazSh 

    Is this faster?

    Sub Doit()
        Dim xFd         As FileDialog
        Dim xFdItem     As String
        Dim xFileName   As String
        Dim wbk         As Workbook
        Dim sht         As Worksheet
        
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        
        Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
        If xFd.Show Then
            xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
        Else
            Beep
            Exit Sub
        End If
        xFileName = Dir(xFdItem & "*.xlsx")
        Do While xFileName <> ""
            Set wbk = Workbooks.Open(xFdItem & xFileName)
            For Each sht In wbk.Sheets
                
                Dim rng As Range
                Set rng = sht.Range("C2:C5000")
                With rng
                    .AutoFilter Field:=1, Criteria1:="="
                    .Offset(1).EntireRow.Delete
                    .AutoFilter
                End With
        
            Next sht
            wbk.Close SaveChanges:=True
            xFileName = Dir
        Loop
    
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
    End Sub

    Remark: opening, saving and closing more than 200 workbooks will always take time, regardless of what you do with those workbooks.

    • ShazSh's avatar
      ShazSh
      Brass Contributor
      Actually these are Google sheets, 1 person works on more than 10+ google sheets in a month so i downloaded them and compile them.

      https://imgur.com/EUchGPP
      I received this error on the line .AutoFilter Field:=1, Criteria1:="="