Compile error: Expected End Sub

dvart

New Member
Joined
Aug 19, 2016
Messages
11
Code:
Private Sub UpdateData()

'Initialise variables
    strDataAddress = wbNB.Worksheets("Calculations").Range("rDataAddress").Value
    
'Open data file
    Workbooks.Open (strDataAddress)
    Set wbData = ActiveWorkbook
    Set wsData = wbData.ActiveSheet

'Delete unrequired rows
    wsData.Rows("1:5").Delete
    
End Sub


Hi

My above code keeps coming up with the error 'Expected End Sub' but i've no idea why. The routine is called from a public routine that is assigned to a button. Can anybody help as it's driving me mental.

Thank you
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hi Steve

This is the code linked to the button and another routine the button calls:

Code:
Public Sub UpdateNBReport()


Application.ScreenUpdating = False


Call ClearData
Call UpdateData


Application.ScreenUpdating = True


End Sub


Private Sub ClearData()


'Declare variables
    Dim ws As Worksheet
    Dim lngRowNo As Long


'Initialise variables
    Set ws = ActiveWorkbook.Worksheets("NBReport")
    lngRowNo = ws.Range("A4").End(xlDown).Row


'Delete rows
    ws.Rows("4:" & lngRowNo).Delete


'Clear data in first row (excluding formulas)
    ws.Range("A3:AA3").ClearContents


Set ws = Nothing


End Sub
 
Upvote 0
I cant see anything wrong there. I presume the wbNB is declared elsewhere. Ive only ever seen the end sub error when it literally isn't present.
 
Upvote 0
Yes, all my declarations are at the top of the module before the routines.

I'm no expert but I really couldn't see anything wrong with this either. When you consider there is a separate sub-routine that is working fine I really cannot understand what is wrong.

Thank you for looking anyway.
 
Upvote 0
The only thing here is that they need to be in the same module. Aside from that there appears nothing else wrong.
 
Upvote 0
Last week i re-wrote the routines into one procedure and it worked. I tried it again this week and i received the same error message (Expected End Sub). I'm sure there is nothing wrong with the code (as it worked), but if someone could suggest some reason why I might be having these problems, I'd be very appreciative.

New code is shown below:

Code:
Public Sub UpdateNBReport()


frmProcessing.Show vbModeless


DoEvents


    Application.ScreenUpdating = False


'Initialise variables
    Set wbNB = ActiveWorkbook
    Set wsNB = wbNB.Worksheets("NBReport")
    lngRowNo = wsNB.Range("A4").End(xlDown).Row
    strDataAddress = wbNB.Worksheets("Calculations").Range("rDataAddress").Value


'Delete data from the NBreport data table
    wsNB.Rows("4:" & lngRowNo).Delete


'Clear data in first row (excluding formulas)
    wsNB.Range("A3:AA3").ClearContents


DoEvents


'Open data file
    Workbooks.Open (strDataAddress)
    Set wbData = ActiveWorkbook
    Set wsData = wbData.ActiveSheet


'Delete unrequired rows
    wsData.Rows("1:5").Delete


'Identify last row no
    lngRowNo = wsData.Range("A1").End(xlDown).Row
    
DoEvents


'Clear any filters in the data table
    wsNB.Range("A3").Select
    ActiveSheet.ShowAllData
    
'Copy the report into the data table
    wsData.Range("A1:AA" & lngRowNo).Copy Destination:=wbNB.Worksheets("NBReport").Range("A3")
    Application.CutCopyMode = False
    wbData.Close savechanges:=False
    
DoEvents
    
'Identify accounts with entry date over 24 months old and delete them
    wsNB.ListObjects("tNBData").Range.AutoFilter field:=29, Criteria1:=False
    
    lngRowNo = wsNB.Range("A3").End(xlDown).Row
    
    wsNB.Range("$A2:$AA$" & lngRowNo).Offset(1, 0).Select
    lngRowCount = Selection.Rows.Count
    lngColumnCount = Selection.Columns.Count
    
    Selection.Resize(lngRowCount - 1, lngColumnCount).EntireRow.Delete
    
    wsNB.Range("A3").Select
    ActiveSheet.ShowAllData
    wsNB.Range("A1").Select
    


    Set wbNB = Nothing
    Set wsNB = Nothing
    Set wbData = Nothing
    Set wsData = Nothing
    
    Application.ScreenUpdating = True
    
    Unload frmProcessing
End Sub
 
Upvote 0
There isn't anything in there. There aren't any macros that aren't initiated manually by the user.
 
Upvote 0
What happens when you press Debug-Compile VBA Project within the VBA window?
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,724
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top