Help with Kill Range Problem

[SDJ98]

Hi,

First off, I want to say that I love this board and it has helped me so many times in the past, but I had to sign up and ask this question which I couldn't find from searching. I'm a VBA idiot, but I'm trying to modify some code (Excel 2003).

The macro is intended to go through a specified list of directory paths to folders, which begin in cell B10 and continue on for 40 or so lines. Its supposed to delete all the files out of these folders. The problem is that not every folder has stuff in it every month, so when this macro comes to that folder, it bombs out and stops. What I want it to do is just ignore that folder and continue. In other words I want the macro to say "if theres stuff in this folder, delete it, then move on to the next folder. If there isn't stuff in that folder, fine, just move on to the next folder"

Macro in question:


Sub Button1_Click()

Range("b10").Select
Do Until ActiveCell.Value = ""
Selection.Copy
Sheets("Directory").Select
Range("b2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Kill Range("Direct") & "*"
Sheets("Rules").Select
ActiveCell.Offset(1, 0).Select
Loop
'
End Sub

For what its worth, I didn't write the macro, nor do I really know how to. I'm trying to learn them by modifying.

Really appreciate the help.

 

[Andrew Poulsom]

Welcome to MrExcel.

Use the Dir function to check that the folder exists (it will return "" if it doesn't).

 

[SDJ98]

The thing is that these folders do exist. Its just that some of them are empty.

When the macro stops it highlights the:

Kill Range ("Direct") & "*"

portion of the macro.

 

[Andrew Poulsom]

It will also return "" if the folder exists but is empty.

 

[SDJ98]

this is so basic that I feel stupid asking, but what/where is the appropriate place to insert that into the macro.

I'll try your way although I keep finding references to "On Error Resume Next", which might do the trick too?

ETA: Situation resolved. On error resume next worked in this situation and I believe its appropriate. Thanks for the help.

 

[Andrew Poulsom]

Try:

If Len(Dir(Range("Direct") & "*")) > 0 Then
    Kill Range("Direct") & "*"
End If

On Error Resume Next is also a reasonable solution.