Add space between rows?

When presenting spreadsheets to suppliers for markup and corrections it would be helpful to have space for them to write or make handwritten notation between rows on the printout. I've tried using alternate rows in Excel, but after a sort, Excel groups them all together again.

Is there a convention for adding space between rows?


TIA,


Ken

Hi,

Do your sort then when ready to send select the range you wish to insert rows and run this macro

The link below shows where to put the code

Press Alt + F8 and select InsertAltRows to run

Sub InsertAltRows()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim i As Integer
    For i = Selection(Selection.Count).Row To Selection(1).Row + 1 Step -1
        Rows(i).EntireRow.Insert
       Next i
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

VBA Noob

You guys are the best.

I'm not familiar with adding a script, but it sounds like it's time I learned.

I'll give it a shot.

Also, being able to set row height is also a solution that will keep it looking right all the time.

Thanks for the quick responses also,



Ken

Thx for the feedback

Hope it helps.

VBA Noob

This could turn into a 'how to run scripts' thread and I don't want to pirate my own thread, but I've checked the links to the help and found with option F-11 I get a couple of windows and in one I can double click on sheet one and get a blank window where I pasted the code and renamed it to InsertAltRows.

Now, I select the rows I want acted upon and just need to know how to activate that code.

Close the VB Editor and then Press Alt + F8 and select the macro named InsertAltRows to run the code

Where to add code
http://www.mcgimpsey.com/excel/modules.html

Getting Started with Macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm

VBA Noob

I know this is an old thread, but today I found it while searching on this problem (so I am posting/sharing my solution) ...
Problem: adding a little height to a series of rows that already have arbitrary/mixed heights.
...via selecting a range of cells in a column and running the following macro code...

Sub IncreaseRowHeightSpacing()
' Select a range of Cells in a Column, then run this to slightly
' Increase the height on each row in selection

    Dim CurrentRowHeight As Single
    Dim PossNewRowHeight As Single

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

j = 1
For Each r In Selection.Rows
    i = r.Row
    If i > 1 Then
        j = j + 1
      With r.Cells(1).MergeArea
                CurrentRowHeight = .RowHeight
                PossNewRowHeight = .RowHeight + 10  'adjust number here to suit
                .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
                  CurrentRowHeight, PossNewRowHeight)
       End With
    End If
    If i = eor Then Exit For 'eor is row number of last row in range
Next r

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Please note, I am a bit rusty and I don't have time to clean up...
...the "MergeArea" is likely not necessary and would do well to be changed for more proper range-object-property, but this should work nonetheless.

So for instance, Column A has been set to a certain width and cells...
A1 = three lines of wrapped info
A2 = one line of data
A3 = two lines of wrapped info

I select "rows" 1-3 and autofit the rows, which snaps then to smallest height.
Then I select "cells" A1-A3 and run the macro to add 10px height to each row.

This is not for use with merged cells... that's a different solution altogether.

Cheers,
TwoHawks