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


rowheight = X

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

  • 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
  • 1
    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
  • 1
    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

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
  • 1
    While this is old, I still found this useful. I revised this version to include a check for the max row height of 409.5. Using this sub would run into an error if the added height exceeded the max row height. Below is what I added to For Each loop. – Alban Feb 6 '15 at 16:39
  • If oRow.RowHeight + dblFactor <= 409.5 Then oRow.RowHeight = oRow.RowHeight + dblFactor Else oRow.RowHeight = 409.5 End If – Alban Feb 6 '15 at 16:42

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.


To expound on the answer from Tiago Cardoso, I added a condition to check to make sure the maximum row height is not exceeded when increasing the row height with the resize factor variable.

Sub FixRowHeight()
    Dim oRange As Excel.Range
    Dim oRow As Excel.Range
    Dim dblFactor As Double

    Set oRange = Sheets(1).Range("A1:O367")
    dblFactor = 12

    For Each oRow In oRange.Rows
        If oRow.RowHeight + dblFactor <= 409.5 Then
            oRow.RowHeight = oRow.RowHeight + dblFactor
            oRow.RowHeight = 409.5
        End If
    Next oRow
End Sub

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.
  • Threw an error for me on this line: cr.rowHeight = newHeight -- "Unable to set the RowHeight property of the Range class" – some1 Jan 18 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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