Paste Special/Row height - code for the taking

[larryww]

Excel's Paste special has column widths but not row heights. This has always been irritating to me when I need to copy some rows and get them to apprear right, or fit on printout page, etc. So I wrote this routine, and am contributing it to the group.

Naturally, improvement or ehhancement ideas are welcome. I certainly expect that one of you can tighten up the first routine by a line or two. (Any other frustrated-ex-C/ASM-coding-perfectionist-dinosaurs out there? )

You can put this in your personal.xls and always have available. I pre-title all my personal macros with pm so they "clump together" in the list of macros when I run them. (If you do this, HIDE personal.xls, and it forms an unobtrusive set of ready-to-use utilities, like the following). Enjoy

'Initial draft - see bottom of thread for final "tweakation"
Sub pmRowHeightsCopy()
' select a range that has the row heights, run pmRowHeightsCopy;
' then move cursor to start of target to apply them to, and run pmRowHeightsPaste
Dim c As Range, i As Integer, iCol1 As Integer
iCol1 = Selection.Column
For Each c In Selection
If c.Column = iCol1 Then
i = i + 1
iHeights(i) = c.Height
End If
Next c
iHeightsCount = i
End Sub
Sub pmRowHeightsPaste()
' select a range that has the row heights, run pmRowHeightsCopy;
' then move cursor to start of target to apply them to, and run pmRowHeightsPaste
Dim c As Range, i As Integer
ActiveCell.Offset(0, 0).Select
For i = 1 To iHeightsCount
Set c = ActiveCell.Offset(0, 0)
c.RowHeight = iHeights(i)
ActiveCell.Offset(1, 0).Select
Next i
End Sub

 

[larryww]

Already, the perfectionist must correct himself - sorry. [red]

Add atop the macro routine:

Global iHeights(65535) As Integer, iHeightsCount As Integer 'for pmCopyRowHeights,pmPasteRowHeights

 

[SkipVought]

Why not just select the rows and with the selection, drag-adjust the RowHeight wysiwyg? Skip,
metzgsk@voughtaircraft.com

 

[dsb]

Few thoughts...

1. are you doing more than Rows.AutoFit would do ?

2. My experience is that when pasting a whole worksheet another whole worksheet, then rows heights are also maintained.

3. Given that there is some reason for doing this row by row
a. don't for loop through each cell of your range,
move by row
b. Make your Height Array a SINGLE since height is
measured in decimal units.
c. Don't do all 1-64K-1 rows, go only to
activesheet.usedrange.row

dsb

 

[larryww]

That worked fine; this works better. I'm surprised nobody pounced on some of the slackness yet

Global iHeights(65535) As Integer, iHeightsCount As Integer 'for pmRowHeightsCopy,pmRowHeightsPaste
Sub pmRowHeightsCopy()
' select a range that has the row heights, run pmRowHeightsCopy;
' then move cursor to start of target to apply them to & run pmRowHeightsPaste
Dim c As Range, i As Integer, iCol1 As Integer
iCol1 = Selection.Column
For Each c In Selection
If c.Column = iCol1 Then
i = i + 1
iHeights(i) = c.Height
End If
Next c
iHeightsCount = i
End Sub
Sub pmRowHeightsPaste()
' select a range that has the row heights, run pmRowHeightsCopy;
' then move cursor to start of target to apply them to & run pmRowHeightsPaste
Dim i As Integer
For i = 1 To iHeightsCount: ActiveCell.Offset(i - 1, 0).RowHeight = iHeights(i): Next
End Sub

 

[larryww]

dsb: we got out of sync so I just saw your responses. This time, they are really good.

single precision - yes, absolutely - good catch.

activesheet.usedrange.row - not sure how to incorporate that with the global. Open for suggestion there.

your item 2.: okay, noted.

don't loop: I'm not sure what you suggest in its place.

And yes, it's doing more than what rows.autofit would do, but still a good observation.

Thanks for your time and thought on this.

 

[dsb]

Here is how I would encorporate both thoughts
I have not tried this..but I know all the parameters are
available...its just a matter of getting the syntax right

Dim lngRow as long, lngH as long
lngH = 0
Redim sngHeight(selection.Row to activesheet.usedRange.Row)
For lngRow = selection.Row to _
activesheet.usedrange.row
lngH = lngH+1
sngHeight(lngH)=Row(lngRow).Height
Next lngRow

 

[larryww]

Nice - i'll chew on this, but will not likely respond until Monday. So it sounds like we could lose the 65535 from the global - that sounds good.

You caught me again - I forgot about integers being signed (ergo < 32,768). Thanks again for that.