ExcelBanter - Conditional Row Delete on Steriods

AA# has to be 0--not just empty, right?

If all your workbooks to be fixed are in a single folder (and nothing else
there), you could try this macro. But I'd keep a backup someplace else--just in
case!

Option Explicit
Sub testme01()

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim Wks As Worksheet
Dim TempWkbk As Workbook
Dim iRow As Long
Dim LastRow As Long
Dim FirstRow As Long
Dim myCol As String

Application.ScreenUpdating = False

FirstRow = 1
'check column AA
myCol = "AA"

'change the folder here
myPath = "C:\my documents\excel\test"
If myPath = "" Then Exit Sub
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.xls")
On Error GoTo 0
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'get the list of files
fCtr = 0
Do While myFile < ""
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
myFile = Dir()
Loop

If fCtr 0 Then
For fCtr = LBound(myNames) To UBound(myNames)
Application.StatusBar _
= "Processing: " & myNames(fCtr) & " at: " & Now

Set TempWkbk = Workbooks.Open(Filename:=myPath & myNames(fCtr))
For Each Wks In TempWkbk.Worksheets
With Wks
LastRow = .Cells(.Rows.Count, myCol).End(xlUp).Row
For iRow = LastRow To FirstRow Step -1
If IsEmpty(.Cells(iRow, myCol).Value) Then
'skip it
ElseIf .Cells(iRow, myCol).Value = 0 Then
.Rows(iRow).Delete
End If
Next iRow
End With
Next Wks
TempWkbk.Close savechanges:=True
Next fCtr
End If

With Application
.ScreenUpdating = True
.StatusBar = False
End With

End Sub

Kirk P. wrote:

I've got 74 Excel files, each with 8 worksheets that I want to delete all
rows where row AA is 0.

The 74 files are located in a path called
"\\oprdgv1\depart\Finance\_Budget\Current Year ePlanning Loads\"

Ideally, it would loop through each file in this path, and delete zero rows
in column AA for each worksheet, then move to the next file and repeat.

--

Dave Peterson