End macro early and close workbook given certain criteria

Question

Below is the final few lines of a long macro that to generate a sales file.

The file I export has a list of 10 digit numbers in column A, with the following code making the final read XXEND followed by a couple of blank lines.

Columns("B:B").Select
Selection.Delete Shift:=xlToLeft

Range("A1").Select
Application.Goto Cells(Rows.Count, "A").End(xlUp).Offset(1), Scroll:=True

ActiveCell.FormulaR1C1 = "XXEND"
Range("A1").Select
Application.Goto Cells(Rows.Count, "A").End(xlUp).Offset(2), Scroll:=True
ActiveCell.FormulaR1C1 = "'"
Range("A1").Select

ActiveWorkbook.SaveAs FileName:="/Users/LucasWayne/Desktop/Websales.txt", FileFormat:=xlText, CreateBackup:=False
Shell ("/Users/LucasWayne/Desktop/WebSalesToK4.app") 
ActiveWorkbook.Close
End Sub

Sometimes my list has no values in column A, with XXEND being the only value in the entire workbook.

If that is the case, before XXEND would be inputted, if there are no values in column A, I would like to terminate the macro early, and instead just close the Excel file without saving.

I highly recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Using `.Select` is a bad practice and makes your code really slow. So try to avoid that. — Pᴇʜ, Sep 11 '18 at 09:49

score 0 · Accepted Answer · edited Sep 23 '18 at 06:52

You can't close the macro and then close the workbook so the code below just exits the macro:

Columns("B:B").Select
Selection.Delete Shift:=xlToLeft

Range("A1").Select
Application.Goto Cells(Rows.Count, "A").End(xlUp).Offset(1), Scroll:=True
If Not WorksheetFunction.Subtotal(103, Range("A:A")) > 1 Then Exit Sub 'This counts the number of cell in column A with something in it and assumes there's a header - if there isn't a header ten just change the 1 to 0
ActiveCell.FormulaR1C1 = "XXEND"
Range("A1").Select
Application.Goto Cells(Rows.Count, "A").End(xlUp).Offset(2), Scroll:=True
ActiveCell.FormulaR1C1 = "'"
Range("A1").Select

    ActiveWorkbook.SaveAs Filename:="/Users/LucasWayne/Desktop/Websales.txt", FileFormat:=xlText, CreateBackup:=False
    Shell ("/Users/LucasWayne/Desktop/WebSalesToK4.app")
ActiveWorkbook.Close

Thank you Jeremy, I sorted it with your line and then just adding a save and close before the exit sub within the IF. — Lucas Wayne, Sep 11 '18 at 10:37

End macro early and close workbook given certain criteria

1 Answers1