Worksheet Function | Screen Updating | Display Alerts | Calculation The mother of all objects is Excel itself. The application object gives access to a lot of Excel related options. As a result, Excel VBA closes your Excel file, without asking you to save the changes you made. If your workbook contains many complex formulas, you can speed up your macro by setting calculation to manual. For example, place a command button on your worksheet and add the following code line: When you click the command button on the worksheet, Excel VBA sets calculation to manual. You can verify this by clicking on File, Options, Formulas. Now when you change the value of cell A1, the value of cell B1 is not recalculated.
Screen Updating = True End With End Sub Sub Delete Blank Rows2() ' Deletes the entire row within the selection if _ some of the cells WITHIN THE SELECTION contain no data. The problem with both i Devlop's and Ashok's answers is that the fundamental problem is an Excel design flaw (apparently) in which the Open method fails to respect the Application. Consequently, setting it to False is of no benefit to this problem.If Patrick Mc Donald's solution is too burdensome due to the overhead of starting a second instance of Excel, then the best solution I've found is to minimize the time that the opened workbook is visible by re-activating the original window as quickly as possible: Dim Temp Wk Bk As Workbook Dim Current Win As Window Set Current Win = Active Window Set Temp Wk Bk = Workbooks. Activate 'Allows only a VERY brief flash of the opened workbook Temp Wk Bk. Visible = False 'Only necessary if you also need to prevent 'the user from manually accessing the opened 'workbook before it is closed. Quit Set app = Nothing I strongly recommend that you lock down the app session before opening the target workbook: ` App.There is a variation of the connection string that we've tested for 2007/2010 files that works as [email protected] Zhang More importantly, it seems the tab name (in the square brackets, with the $ at the end) has some funny restrictions that I can't seem to locate at the moment. The Jet OLEDB drivers have a memory leak when reading Excel (all versions, and the predecessor ODBC drivers): you're going to get error messages about available memory if you do this more than once in any user session. This is great if the Excel file your reading from is intended as a data source for validation.Sub Delete Blank Rows1() ' Deletes the entire row within the selection if the ENTIRE row contains no data.