+ Reply to Thread
Results 1 to 2 of 2

Macro to Paste Special Row Heights

Hybrid View

Guest Macro to Paste Special Row... 03-22-2005, 09:08 PM
ajswanso Re: Macro to Paste Special... 08-29-2019, 01:28 PM
  1. #1
    Hash@example.org
    Guest

    Macro to Paste Special Row Heights

    All -

    The Paste Special menu has often frustrated me, for I would often make
    three trips there for effectively one pasting: 1. Paste Special
    Values, 2. Paste Special Formats, and 3. Paste Special Column Widths.

    This week I acted upon my frustration and made a userform in
    Personal.xls that replaces the Paste Special radio buttons with
    check-boxes. Works fine, does it all in one step. The code for the
    check-boxes as true is the code from the recorder, fixing the holes for
    column width's and data validation's missing constants (8, 6).

    "Clever," says I. "Why don't you add to it and do a Paste Special Row
    Heights?"

    Why? Because I can't figure out how to get at the row data of the
    source (copied) range. You select a range and copy, and then select
    usually a single cell and paste. How via VBA do I know the row
    dimensions of what is in memory--the former selection? I found code on
    Google Groups that does this by input boxes. Not what I want.
    Microsoft figured it out for columns.

    What's the trick?


    Thanks in advance.

    ....best, Hash

  2. #2
    Registered User
    Join Date
    08-29-2019
    Location
    Raleigh, NC
    MS-Off Ver
    2010
    Posts
    1

    Cool Re: Macro to Paste Special Row Heights

    First select the rows you want to copy, and in the code below, type the range you want to have the same row heights as the selection.
    This is the range A40:A60 section. Then run your code and the row heights will copy.

    Sub CopyRowHeights()
    Dim r As Long
    With Selection.Cells
    For r = 1 To Selection.Rows.Count
    Range("A40:A60").Rows(r).RowHeight = .Rows(r).RowHeight
    Next r
    End With
    End Sub

    Alternatively, if you wanted to select each range from the macro without having to program it in, you can do it that way.

    Sub CopyRowHeights()
    Dim r As Long
    Dim CopyFrom As Variant, CopyFromCount As Variant, CopyTo As Variant

    On Error Resume Next
    Set CopyFrom = Application.InputBox("Select your range to copy from", "Hello", , , , , , 8)
    Err.CLEAR
    On Error Resume Next

    CopyFromCount = CopyFrom.Rows.Count

    On Error Resume Next
    Set CopyTo = Application.InputBox("Select your range to copy to", "Hello", , , , , , 8)
    Err.CLEAR
    On Error Resume Next

    With CopyFrom
    For r = 1 To CopyFromCount
    CopyTo.Rows(r).RowHeight = .Rows(r).RowHeight
    Next r
    End With
    End Sub
    Last edited by ajswanso; 08-29-2019 at 02:03 PM. Reason: Alternative method to complete the same task

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1