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
|
Similar Threads
