VBA End if issue

I need your advice peers!

I keep getting an error message that says "End if  without block if" error message.

Can anyone advise what I am doing wrong.



Sub LoopAllFilesInFolder()
Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


myPath = "C:\Users\Zack Carter\Desktop\DUNPOS0475 DUNPOS0475 CURTAINS Pencil Pleat Hanging .v1"
myExtension = "*.xlsx*"

myFile = Dir(myPath & myExtension)
Do While myFile <> ""
If Len(myFile) = 0 Then Exit Do
Set wb = Workbooks.Open(fileName:=myPath & myFile)

Columns("P:XFD").entireColumns.Delete

wb.Close SaveChanges:=True
End If

myFile = Dir
Loop

'Message Box when tasks are completed
MsgBox "Task Complete!"
ResetSettings:
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Open in new window

zack carterProject AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Zack

The only If you have in the code is this,
    If Len(myFile) = 0 Then Exit Do

Open in new window

which is a single line If so doesn't need an End If.

So all you need to do to get the code to compile is remove the End If on line 23.
0
zack carterProject AnalystAuthor Commented:
Hi Norie,

I have removed the End IF statement and its  not looping through the fodler and deleting the columns.

Now im stumped.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
After removing the line#23, you also need to correct the path at line#12 which is missing a backslash in the end of the file path.
It should be like this...

myPath = "C:\Users\Zack Carter\Desktop\DUNPOS0475 DUNPOS0475 CURTAINS Pencil Pleat Hanging .v1\"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

NorieAnalyst Assistant Commented:
Zack

What files are you looking for and in which folder?
0
zack carterProject AnalystAuthor Commented:
thank you for your feedback and spotting my mistake
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Zack!
0
Fabrice LambertFabrice LambertCommented:
Additional notes:
Indentation will help reading and understanding your code (that include us and you).
Keep in mind that source codes are supposed to be working AND read by people, so write it as understandable as possible.

I do not advice using the Dir function, I prefer using the FileSystemObject library wich is more secure.
Since the Dir function store somewhere the last criteria used for loop purpose, what if in your loop you call a function that change the criteria ?
Your code will be screwed (and you'll wonder why).
Consider the following:
    '// myFunction is supposed to print all txt files
    '// Alas, it will print 1 txt file and all xlsx files
Public Sub myfunction()
    Dim fileName as String
    fileName = Dir(CurrentProject.Path & "\*.txt")

    Do While fileName <> vbNullString
        Debug.Print fileName
        myFriendMessyfunction
    Loop
End Sub

    '// function written by someone else
    '// you arn't supposed to modify it
Public Sub myFriendMessyfunction
    Dim fileName as String
    fileName = Dir(CurrentProject.Path & "\*.xlsx")

    Do While fileName <> vbNullString
        '// random code here
    Loop
End Sub

Open in new window

On the other hand, this version will do the job as expected:
Public Sub myfunction()
    Dim FSO As Object		'// Scripting.FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Dim fld As Object		'// Scripting.Folder
    Set fld = FSO.GetFolder(CurrentProject.Path)
    
    Dim file As Object		'// Scripting.File
    For Each file In fld.Files
        If (file.Name Like "*.txt") Then
            Debug.Print file.Name
        End If
        myFriendMessyfunction
    Next
    Set fld = Nothing
    Set FSO = Nothing
End Sub

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.