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...
HansVogelaar
Oct 07, 2021MVP
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.
- ShazShOct 08, 2021Brass 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:="="- HansVogelaarOct 08, 2021MVP
ShazSh I'd have to see the workbook for which the code fails.