Results 1 to 13 of 13
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    copying row heights (Excel2000)

    The Edit-PasteSpecial facility lets me easily copy a range of column widths but it lacks a similar feature for row heights.
    I want to copy a block of data rows and want to preserve the row heights already set within the block.
    Normally, I would copy the entire sheet (which will preserve all row height settings) but in this particular case there are other things I need to keep on the destination sheet.
    I hate having to do this manually.
    Does Legare have a ready-made loop for doing this?

    zeddy

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying row heights (Excel2000)

    If you select a range of cells, the code below will display an Input box asking for a target cell. If you then click on a cell, the code will copy the row heights from the selection to the row of the cell you clicked on and the rows following.

    <pre>Public Sub CopyRowHeights()
    Dim oTgt As Range, I As Long, J As Long, dHeight As Double
    Set oTgt = Application.InputBox("Select target cell", Type:=8)
    J = 0
    For I = 1 To Selection.Rows.Count
    oTgt.Offset(J, 0).EntireRow.RowHeight = Selection.Rows(I).RowHeight
    J = J + 1
    Next I
    End Sub
    </pre>

    Legare Coleman

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: copying row heights (Excel2000)

    You can highlight the rows you want to copy
    select the destination rows
    edit -paste special -formats

    row formatting is copied when you select rows, like column formatting is copied when you select columns

    Steve

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: copying row heights (Excel2000)

    Many thanks Legare!

    You are a real gem.
    Your code works a treat.
    I shall save this to my bag of tricks for future use.
    Once again your loopy loops strike gold.
    Why don't you write a book??!!!

    zeddy

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: copying row heights (Excel2000)

    Hi Steve,

    ..row heights never seem to copy for me with edit-paste special formats.
    Maybe they only do if you are using auto-format row heights e.g. when using larger fonts.
    When you have lots of manually set row heights, e.g. 5, 30, 20 etc it doesn't seem to work.
    Many thanks for your response though.

    zeddy

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying row heights (Excel2000)

    If you copy a full row, and then do a Paste Special Formats, the row height should be pasted along with the rest of the formats. It should not matter what the height is, or what the font is. Can you upload a workbook where you can't get it to work?
    Legare Coleman

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: copying row heights (Excel2000)

    Hi Legare and Steve

    Copying entire rows does work - using paste special formats the row heights will copy correctly.
    Unfortunately, all of the formats in the entire row will also be copied.
    I often need to retain colour-coding on different sheets, but want report layouts in certain blocks to be similar i.e. have identical row heights.

    In the sample file attached, if you copy a block of data from the source sheet and try any kind of pasting to a new sheet, some row heights won't be the same.

    zeddy

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying row heights (Excel2000)

    I think that you have some corruption in that workbook. As you sent it to me, you are absolutely correct. If I copy the rows from Sheet1 and paste formats to another sheet, the row ight for that row is not correct. If I save the file to HTML format, then open the HTML file and save it back as .xls, then the row height copies and pastes correctly.
    Legare Coleman

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: copying row heights (Excel2000)

    Copying the column width does the same thing, it copies the format for all the cells in the column, so I don't see how the copying rows is any different than copying columns.

    Steve

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying row heights (Excel2000)

    In the Paste Special menu there is an option to paste column widths...it's the last option in the Paste menu in the top section. There is no such option for row height in the menu. I believe that is his problem.

    -Brett

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: copying row heights (Excel2000)

    Thanks for the additional info.
    I did not realize that XL2000 has this "special option". It is not available as an option in the XL97 version that I use.

    Steve

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: copying row heights (Excel2000)

    Hi Steve,

    You can copy column widths only (i.e. leaving eveything else in the column in whatever format you want).
    You can't copy row heights only (you can only do it at the expense of also copying all cell fornmats within each rows copied)

    Legare's routine provides a useful fix for many situations I find myself in.

    zeddy

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: copying row heights (Excel2000)

    Hi Legare,

    ..so it wasn't me then.
    I inherited the original source file.

    I shall try out some fresh files.

    Many thanks for your help.

    zeddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •