Any option to configure spacing between rows?

ghumdinger · #1 09-19-2011, 04:20 AM

Hi all,

This seem like a rather elementary q, but I can't find a way to do it.

Is there a way to configure some spacing between all the rows, quite like Word's para spacing, say X pixels before and after?

Adjusting the row height doesn't quite help, as the rows in my spreadsheet are of different row heights.

Cheers,
Jay

Catalin.B · #2 09-19-2011, 04:54 AM

try this:

Code:

Sub rowHeight()
Dim i As Integer
Application.ScreenUpdating = False
For i = 1 To 100 Step 2
Range("A" & i).rowHeight = 15
Next i
Application.ScreenUpdating = True
End Sub

ghumdinger · #3 09-20-2011, 10:39 PM

Hi Catalin,

Thanks for your suggestion. I've tried it but it does not seem to work. Perhaps it's because some of my columns have word wrap turned on.

I've tried to reproduce the problem in a simpler worksheet. I created three cells. Each of them have different heights due to the different lengths of information in the cell with word wrap turned on.

The macro reduces the height of cell A3 from 28 to 15, while the other 2 cells' height remain the same.

I've uploaded the sample workbook.

I'm trying to increase the "top and bottom" spacing between the cells by a fixed amount.

Catalin.B · #4 09-20-2011, 10:51 PM

insert in a column of your choice this formula:
=CEILING(18*LEN(A4)/110;18)
where A4 is the cell to evaluate, 110 is the desired number of characters in one row of the same cell and 18 is your desired default height...adjust as necessary...
copy this formula down to all rows in column, then use this macro to adjust
row height :

Code:

Sub Height()
Dim planWks As Worksheet
Dim nextRow As Long
   Set planWks = Worksheets("Plan")
Dim i As Integer

Application.ScreenUpdating = False

For i = 1 To 100
    If planWks.Range("U" & i).Value > 0 Then
        Rows(i & ":" & i).RowHeight = planWks.Range("U" & i).Value
    End If
Next i
Application.ScreenUpdating = True
End Sub

change sheet name , range to evaluate and column letter as needed...
In this macro, the column which has the necessary row height (calculated with the initial formula) is column "U"

ghumdinger · #5 09-21-2011, 02:25 AM

Catalin,

I admit this is quite over my head. It'll probably be for the best if I tackle if after reading up on formulas & macros.

Thanks for your help anyway. I'll mark this as solved,

Best,
Jay

Catalin.B · #6 09-21-2011, 02:52 AM

Don't give up so soon...
Have a look at the sample attached (make sure you activate macros on opening)
if you do not want to insert a new row at every row, delete the line from code with the expression entirerow.insert..

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
how to configure outlook?	testbysunny	Outlook	2	07-18-2014 11:18 PM
Configure Outlook 2000 with AOL	Foggy	Outlook	2	02-10-2011 10:12 AM
Can not Configure Outlook 2007 for the first time	MSdBaSqL	Outlook	1	12-25-2010 06:56 PM
Configure MailBox outlook	rmramprabhu	Outlook	0	05-16-2010 03:04 PM
Office programs attempt to configure when opened	Lewis Mcgee	Office	2	12-04-2009 09:49 AM