vba to paste height and width of selected cell

[davers5]

I'd like to create a shortcut key to paste the row height and column width of a copied cell. The column width part is easy because you can get that from paste special but the row height you can't get from there. My question boils down to this: how do I get the row height (or other properties for that matter) from a cell that has already been copied?

Just to be more clear:
A user selects cell a1 which has a row height of 15 and presses ctrl-c to copy the cell. I then want to be able to click a button that will give any other cell a height of 15.

Thanks!

Dave

 

[VoG]

Rows("1:1").RowHeight = 15


and assign a button

 

[davers5]

Thanks for the response. That would work if I new the height was 15 everytime. I want to take the height of any cell and then make any other cell the same height regardless of the actuall value of the height. It's not always the same height.

 

[Joe4]

See if you can use some of the concepts from the VBA code below.

This will take the row height of the current row and that you are currently on and "copy" that height to another row.

Sub MyAdjustHeight()

    Dim myHeight As Double
    Dim myRow As Long
    
'   Capture height of current row
    myHeight = Selection.RowHeight

'   Ask user what row to copy height to
    myRow = InputBox("What row would you like to copy the height to?")
    If IsNumeric(myRow) Then Rows(myRow).RowHeight = myHeight
    
End Sub

 

[davers5]

Thanks again for the response. This is very similar to code I have written. Is there a way, however, to get the properties from a cell or range that is already copied and then give those properties (specifically the rowheight property) through pasting or setting properties or whatever to a cell that is selected.

Basically, I"m looking for a shortcut key. I press ctrl-c to copy the origianl cell and then press ctrl-r (for instance) to run my code and paste the row height to another cell.

 

[stanleydgromjr]

Dave,

Copy the below code and paste into a code Module.

'--------------------Code Begins Here--------------------

Public sRowHeightHold As Single
Public sColumnWidthHold As Single


Sub CopyRowHeightColumnWidth()
'
' CopyRowHeightColumnWidth Macro
' Macro created 9/18/2006 by Stanley D. Grom, Jr.
'
Keyboard Shortcut: Ctrl+Shift+C
'
Static sRowHeight As Single
Static sColumnWidth As Single

sRowHeight = ActiveCell.Height
sRowHeightHold = sRowHeight
MsgBox "sRowHeight = " & sRowHeight

sColumnWidth = ActiveCell.Width
sColumnWidthHold = sColumnWidth
MsgBox "sColumnWidth = " & sColumnWidth

End Sub


Sub PasteRowHeightColumnWidth()
'
' PasteRowHeightColumnWidth Macro
' Macro created 9/18/2006 by Stanley D. Grom, Jr.
'
' Keyboard Shortcut: Ctrl+Shift+V
'
MsgBox "sRowHeightHold = " & sRowHeightHold
Selection.RowHeight = sRowHeightHold

MsgBox "sColumnWidthHold = " & sColumnWidthHold
Selection.ColumnWidth = sColumnWidthHold / 5.46075663466968

End Sub

'---------------------Code Ends Here---------------------


Manually change the width and height of a cell.

Run the 'CopyRowHeightColumnWidth' macro, Keyboard Shortcut: Ctrl+Shift+C

Move to another cell, different row and column.

Run the 'PasteRowHeightColumnWidth' macro, Keyboard Shortcut: Ctrl+Shift+V

When satisified with the macros, rem out/add an ' in from of the 'MsgBox' lines of code.


I am using Windows XP Professional SP2, and Excel 2003 SP2.

Have a great day,
Stan

 

[Greg Truby]

Stan's got you sorted out. You need two macros for this, one to "copy" and one to "paste". This stems from the fact that AFAIK there is no danged way to tell what cell(s) are on the doggone clipboard. You would think this would be possible, but I posted the question years ago and in all the threads I've worked, I've never seen anyone do it. Of course, if anyone does know how to just read the clipboard and tell the address of the cell(s) on it, I would be very, very glad to hear how. I mean by somehow reading it from the clipboard; not a complex workaround like tacking code into a selectionchange event handler and tracking the .CutCopyMode status and when it flips pulling down the last selected address that has been getting tracked in the SC event handler. That kind of nonsense I could write myself, but that's certainly going the "long way around the barn".

 

[davers5]

Stan and Greg,

Thanks, this is great. I really wanted to know how to get the stuff off the clipboard, I guess, but since Excel doesn't seem to store a rowheight property in there, I guess you can't get at it. Like Greg I thought there had to be some reference to the range or something on the clipboard but I guess this is not the case.

Thanks again for the help.

Dave

 

[ILv2Xlr8]

So is there not an easy way to copy a select a set of rows and paste just the row height to another set of rows?

 

[Greg Truby]

No. Please refer to my post on this thread for why this is so. Please refer to stanley grom's post for a reasonable workaround.