Forum Discussion
ShazSh
Oct 07, 2021Brass Contributor
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 Sub3 Replies
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 SubRemark: opening, saving and closing more than 200 workbooks will always take time, regardless of what you do with those workbooks.
- ShazShBrass ContributorActually 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:="="ShazSh I'd have to see the workbook for which the code fails.