Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have this report and I want to increase the vertical cell padding to make it easier to read.

Now, typically you could just select multiple rows and change the row height until you're satisfied.

However, in this case, I have cells with wrapped text and multiple lines. I can't change change the the row height, because the wrapped text will get cut off.

What I want to do is increase the row height of a bunch of cells by a certain value. Rather than just change it to a specify value. ie.

rowheight = rowheight + X

Not

rowheight = X


Can you do this in excel? Will I need VBA?

share|improve this question
    
I'd guess you'll eventually have at least some VBA here. Therefore... I'd move this question to SO instead of keeping it here in SU. –  Tiago Cardoso Aug 1 '11 at 14:36
    
I don't see any reason to. If VBA is required, your code is perfect. Just needs to be changed to use a selection rather than a static range. –  user606723 Aug 1 '11 at 14:48
    
IMO... SU: Excel-only tricks and functions. SO: Excel/VBA related tricks and functions. –  Tiago Cardoso Aug 1 '11 at 14:53
    
I guess the point is that... I could've done it with VBA myself. I was looking for a answer from the perspective. If I asked at SO.... I -definitely- would've gotten a VBA answer. –  user606723 Aug 1 '11 at 15:00
    
So, your idea was to do it without using VBA? If so, you should've been clearer in your question... Anyways, I don't believe it's possible. –  Tiago Cardoso Aug 1 '11 at 15:08

3 Answers 3

up vote 1 down vote accepted

I'd go for the code below... not sure if there would be a way to apply it for all rows once. I'd be glad (and upvote) if someone else here knows :)

Sub fixRowHeight()

Dim oRange As Excel.Range
Dim oRow As Excel.Range
Dim dblFactor As Double

Set oRange = Sheets(1).Range("A1:B25")
dblFactor = 5

For Each oRow In oRange.Rows

    oRow.RowHeight = oRow.RowHeight + dblFactor

Next oRow

End Sub
share|improve this answer

I know this is old, but I'll put this solution out there because this page ranks well and I have a helpful answer that I haven't found elsewhere. I've just hit this issue and the above solution is not satisfactory in some circumstances.

  1. Insert a column somewhere - probably column A. Select the entire column.
  2. Give it top/bottom borders of the thickest kind - the solid fat bar - apply/ok.
  3. Select all other columns that you'd like to be padded. Vertically center them.
  4. Hide the bordered column & save.

This gives you a top and bottom padding of approximately 3 units/5px top & bottom. With arial, it's enough to keep tops and tails from touching the cell edges.

The VBA code provided is good. But it fails as soon as your spreadsheet is viewed by someone who needs to narrow up some columns and so removes manual formatting or applies their own. If the user knows what they're doing, they will hit Ctrl + A and double click a row indicator to auto-height all rows.

Using the borders provides a solution that will always work regardless, unless manually formatted away.

share|improve this answer

Here's a macro to help you out.

First it sets the vertical alignment for each cell to Center.

Next, it takes the current height of each cell and adds 7.5 to it (this is equivalent to 10 pixels). In the end, it adds a 5-pixel padding to the top and bottom part of the cell. Just adjust this value according to your preference.

Sub PadCells()

Set cr = Selection

For Each cr In cr.Rows
    newHeight = cr.RowHeight + 7.5 
    cr.VerticalAlignment = xlCenter
    cr.RowHeight = newHeight
Next cr

End Sub

To use:

  1. Select the cells for which you want to adjust the height.
  2. Run the macro.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.