data table

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

TrustPilot

starstarstarstarstar Excellent

  • Home
  • Courses
  • Promotions
  • Schedule
  • Formats
  • Our Clients

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Data in table

Data in table

resolvedResolved · Low Priority · Version Standard

josephine has attended:
Excel Intermediate course

Data in table

can you automatically insert a blank line in between each row of data in table

Inserting blank rows between data in table

Hi Josephine

As I explained yesterday this would need some VBA code. So do the following steps:

1. Open the Workbook in which you need to insert the rows.

2. Open the Tools menu Select Macro and Click the Visual Basic Editor (VBE)

3. In the VBE Open the Insert menu and Click Module. This inserts a new Module.

4. Copy the following code and paste into the new module (Module1)


Sub InsertBlankRows()

'Select last row in worksheet.
Selection.End(xlDown).Select

Do Until ActiveCell.Row = 1

'Insert a blank row
ActiveCell.EntireRow.Insert Shift:=xlDown

'Move up one row.
ActiveCell.Offset(-1, 0).Select

Loop

End Sub


5. Close the VBE.

Back in the workbook

6. Click the first cell of the first row of the table eg A1

NB Ensure that there are no blank cells in this column as the system will not do anything past this

7. Open the Tools menu Select Macro and Click Macros

8. In the Macro dialog box Select the InsertBlankRows macro and click Run

The system will insert a blank row between each record

Hope this helps

Carlos


 

Excel tip:

Creating a range of monthly payments as text

You could use a formula to create a range of payment ie. payment amount for x% to y% rate with fixed terms and principle.

The text that would be "between Xamount and Yamount".

Here is how to do it.

1 Use the PMT function to get your monthly payments figure or whatever frequency of payments that you choose he start range.

See PMT under Excel Help

2. Nest these in the ROUND function to round decimals see ROUND under Excel Help


3. Concatenate this using "&" and concatenate " to " and concatenate "Between ".

4. Concatenate the above to PMT function for the end range

ie.

="Between "&ROUND((PMT1),decimal places)&" and "&ROUND((PMT2),decimal places)

View all Excel hints and tips


Server loaded in 0.08 secs.