Follow these steps to clean up the macro:

1. Theworkbook.openText lines are fine as recorded.

2. The following lines of code attempt to locate the final row of data so that the program knows where to enter the total row:

Selection.End(xlDown).Select You don't need to select anything to find the last row. Also, it helps to assign the row number of the final row and the total row to a variable so that they can be used later.To handle the unexpected case where a single cell in Column A is blank, start at the bottom of the worksheet and go up to find the last used row:

1 Find the last row with data. This might change every day FinalRow = Range("A65536").End(xlUp).Row TotalRow = FinalRow + 1

3. These lines of code enter the word "Total"in Column A of the total row:

Range("A14").Select ActiveCell.FormulaR1C1 = "'Total"

The better code will use the TotalRow variable to locate where to enter the word "Total."Again,there is no need to select the cell before entering the label:

1 Build a Total row below this Range("A" & TotalRow).Value = "Total"

4. These lines of code enter the Total formula in Column E and copy it over to the next two columns:


Selection.FormulaR1C1 = "=SUM(R[-12]C:R[-1]C)"

Selection.AutoFill Destination:=Range("E14:G14"), Type:=xlFillDefault Range("E14:G14").Select

There is no reason to do all this selecting.The following line enters the formula in three cells.The R1C1 style of formulas is discussed completely in Chapter 6,"R1C1 Style Formulas":

Range("E" & TotalRow).Resize(1, 3).FormulaR1C1 = "=SUM(R2C:R[-1]C)"

5. The macro recorder selects a range and then applies formatting:

Rows("1:1").Select Selection.Font.Bold = True Rows("14:14").Select Selection.Font.Bold = True

There is no reason to select before applying the formatting.These two lines perform the same action and do it much quicker:

Rows(TotalRow & ":" & TotalRow).Font.Bold = True

6. The macro recorder selects all cells before doing the AutoFit command:



There is no need to select the cells before doing the AutoFit:


7. The macro recorder tags each macro with a line that the macro was recorded on a certain date:

' Importlnvoice Macro

1 Recorded 10/23/2003 by Bill Jelen This macro will import invoice.txt ' and add totals.

Now that you have changed the recorded macro code into something that will actually work,go ahead and change the "Recorded"to reflect the fact that you've written useful code:

1 Importlnvoice Macro

1 Written 10/23/2003 by Bill Jelen This macro will import invoice.txt ' and add totals.

Here is the final macro with the changes:

Sub ImportInvoiceFixed() ImportInvoice Macro

Written 10/23/2003 by Bill Jelen This macro will import invoice.txt and add totals.

Keyboard Shortcut: Ctrl+i

0 0

Post a comment