Separating Records in Excel

When importing date into an AccountEdge company file, records are separated by blank lines. Each Invoice record can have several lines (line items) per invoice. These lines are not separated by a blank line.

Journal Entry records can also have several lines per record. A blank line in the input file would indicate a new Journal Entry record.

Frequently, you have an import list with no record separation, but you would like each record to be a separate transaction (entry). This can be resolved by reading the input file into Excel, and using the procedure below to add blank lines between records. This procedure will add a blank line after each line in the Excel file.

In Excel, to add a blank like after every line:

  1. Press ALT+F11 to start the Visual Basic Editor.
  2. On the Insert menu, click Module.
  3. In the module sheet, type or paste the following code:

    Sub Insert_Blank_Rows()
      Selection.end(xldown).select
      Do Until ActiveCell.row = 1
       ActiveCell.EntireRow.Insert shift := xlDown
       ActiveCell.Offset(-1,0).Select
      Loop
    End Sub
     
  4. Press ALT+F11 to return to Excel.
  5. Select cell A1 or any cell that contains data.
  6. Press ALT+F8 to open the Macro dialog box.
  7. In the Macro name list, click Insert_Blank_Rows, and then click Run.

To remove blank lines in an Excel file:

  • First, before you make any drastic changes to any workbook always make sure you have a backup copy or a recently saved copy in case you need to restore the original data
  • Now, select the cells in one column from the top of your list to the bottom
  • Make sure that all the blank cells in this selected range are the rows you want to delete
  • Press the F5 key on your keyboard (or select Edit, Goto)
  • Click the Special... button
  • Click the Blanks option and click OK. This will select all blank cells in the range you had previously selected
  • Now choose Edit, Delete, select the Entire Row option and click OK