How to change column width and row height

[JenniferMurphy]

I just learned a very helpful way to change a range of columns to a set width using paste special from this page:

Change the column width and row height

But of course, in typical m$ft fashion, they only provide half-baked solutions. Even though the title says "column width and row height", the text only deals with column widths. And the paste special options do not include row height as an option.

Is there any comparable way to change a bunch of rows to the same specific height? I don't mean by dragging the border. I mean by matching some existing cell height like what works for columns.

 

[Marcelo Branco]

Maybe something like this

Sub aTest()
    'Copy the row already set with the desired height
    Rows("2:2").Copy
    
    'Paste special on target rows
    Rows("5:6").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

M.

 

[JenniferMurphy]

Maybe something like this

Sub aTest()
    'Copy the row already set with the desired height
    Rows("2:2").Copy
   
    'Paste special on target rows
    Rows("5:6").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

M.

Interesting. How would it be called? How would I tell it which row is the source and which the targets?

 

[Marcelo Branco]

Interesting. How would it be called? How would I tell it which row is the source and which the targets?

I assumed this was already known, otherwise how to do it?

M.

 

[JenniferMurphy]

I assumed this was already known, otherwise how to do it?

M.

It is known for any specific situation, but it is not a constant.

On one sheet, I might want to set rows 5-20 to the same height as row 7. In another sheet, it might be setting rows 5-9 to match for 4. In your sub, those row numbers and hard coded. I need them to be variables (parameters).

Maybe there is something I am missing. . .

 

[Marcelo Branco]

Try for Sheet1

Sub aTest()
    Dim SetRow As Long, Target1 As Long, Target2 As Long
    
    SetRow = 7
    Target1 = 5
    Target2 = 20
    
    'Copy the row already set with the desired height
    Rows(SetRow).Copy
    
    'Paste special on target rows
    Rows(Target1 & ":" & Target2).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

M.

 

[GWteB]

@JenniferMurphy, do you mean something like this?

Sub JenniferMurphy()

    With Worksheets("Sheet1")
        AdjustRows .Rows(7), .Rows("5:20")
    End With
    With Worksheets("Sheet2")
        AdjustRows .Rows(4), .Rows("5:9")
    End With

End Sub


Sub AdjustRows(ByVal argRefRow As Range, ByVal argTargetRows As Range)
    argTargetRows.RowHeight = argRefRow.RowHeight
End Sub

 

[JenniferMurphy]

Try for Sheet1

Sub aTest()
    Dim SetRow As Long, Target1 As Long, Target2 As Long
 
    SetRow = 7
    Target1 = 5
    Target2 = 20
 
    'Copy the row already set with the desired height
    Rows(SetRow).Copy
 
    'Paste special on target rows
    Rows(Target1 & ":" & Target2).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

M.

Unless I am missing something, all you did was move the constants from inside the commands to variables that are, themselves, constants. Instead of declaring them as Dim, you could have declared them as Const.

How do I pass the values in those parameters to the Sub so that it will work for any source and target rows?

I might want to set rows 5:20 to match row 7 one time and set them to match row 12 another time -- or any other row.

 

[Marcelo Branco]

Jennifer,

Couldn't you, for each sheet, change the values of the variables? That is my idea...
Maybe i'm not understanding what you're trying to do...

M.

 

[JenniferMurphy]

@JenniferMurphy, do you mean something like this?

Sub JenniferMurphy()

    With Worksheets("Sheet1")
        AdjustRows .Rows(7), .Rows("5:20")
    End With
    With Worksheets("Sheet2")
        AdjustRows .Rows(4), .Rows("5:9")
    End With

End Sub


Sub AdjustRows(ByVal argRefRow As Range, ByVal argTargetRows As Range)
    argTargetRows.RowHeight = argRefRow.RowHeight
End Sub

The AdjustRows Sub now has parameters, but it is called by the JM sub which does not. If I want to run this from a sheet, how do I get the row numbers that I want passed to either Sub?