Deleting rows from list of files

[italia]

I am trying to delete some rows (3 to 200) in all the excel files in
directiory "C:\Excel" using a macro.
Following is the code that I am using. It deletes the rows in the
current worksheet (worksheet where the macro exists). I know I am not
referencing it properly. Please help.

Thanks,
Italia


Sub testme()

Dim i As Long
Dim newwb As Workbook
Dim j As Long
Dim rng As Range

Const myfolder As String = "C:\Excel\"
With Application.FileSearch
..NewSearch
..LookIn = myfolder
..SearchSubFolders = False
..Filename = "*.xls"

If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Set newwb = Workbooks.Open(Filename:=.FoundFiles(i))

'Deleting code starts here

For j = 2000 To 3 Step -1
If WorksheetFunction.CountA(Selection.Rows(j)) = 0 Then
Sheet1.Rows(1).EntireRow.Delete
MsgBox "Hello"
End If
Next j

'Deleting code ends here

newwb.Close savechanges:=True
Next i
Else
MsgBox "There were no files found."
End If
End With

End Sub

 

[Bernie Deitrick]

Italia,

Fully qualify your ranges, perhaps along the lines of:
If WorksheetFunction.CountA(newwb.Activesheet.Selection.Rows(j)) = 0 Then
newwb.Activesheet.Selection.Rows(j).EntireRow.Delete


HTH,
Bernie
MS Excel MVP

 

[italia]

Thanks Bernie-

I tried your statement but it gives the following error-

Run-time error "438"
Object doesn't support this property or method

Also please advice on a way to make this program faster. Is there a way
we can modify the excel files without opening them?

-Italia

 

[Bernie Deitrick]

Try:

If WorksheetFunction.CountA(Selection.Rows(j)) = 0 Then
Selection.Rows(j).EntireRow.Delete
MsgBox "Hello"
End If

You must open the files to modify them.

HTH,
Bernie
MS Excel MVP

 

[italia]

Thanks again !!!

I tried your suggestion. It does not delete anything unless it is
selected.
Is there a way that I can specify a range (say A3 to G200) and delete
this range?

-Italia

 

[Bernie Deitrick]

Italia,

Change

Selection

to

Range("A1:G200")

HTH,
Bernie
MS Excel MVP

 

[italia]

I really appreciate your help.

I changed the Selection to Range ("A1:G200"). It is deleting rows from
the current file (worksheet where the macro exists) and not from any
other files.
We need to specify the particular file ("newwb") that we are opening. I
just done know how.

Thanks,
Italia

 

[Bernie Deitrick]

Italia,

Try:

For j = 200 To 1 Step -1
If WorksheetFunction.CountA(newwb.Worksheets("Sheet1") _
.Range("A1:G200").Rows(j)) = 0 Then
newwb.Worksheets("Sheet1").Range("A1:G200") _
.Rows(j).EntireRow.Delete
End If
Next j


HTH,
Bernie
MS Excel MVP

 

[italia]

It give the following error-
Run-time error '9':
Subscript out of range

-Italia

 

[italia]

Hi Bernie-

I used the following and it did the trick.

newwb.Sheets(1).Rows("3:200").EntireRow.Delete
Thanks for your help.

Regards,
Italia